SQL Server - consolidate data

Question

Below is a query and result

Query

SELECT Job#, Doc#, Value, Description FROM Table

Result

Job#    Doc#,    Value,     Description
1000     1         100       paint house
1000     2           0       clean floors
1001     1          90       install roof
1001     2           0       install boiler
1001     3           0       install elevator

I would like to create single row by Job# where Doc# = 1, but add the Description where Doc# > 1 to the single row as new fields, i.e if there are 15 Documents Doc#(1-15) then there should be 14 new fields for that row and would look like this:

Desired Result

Job#    Doc#,  Value,  Description,  Desc2,          Desc3,           ... Desc14
1000     1       100   paint house   clean floors
1001     1        90   install roof  install boiler  install elevator

How can I write my query to achieve this desired result?

Splitting each additional row into new columns is going to be tricky, and may rely on the use of a [Dynamic Pivot](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query). However, if you were okay with having one field as "Description" and comma separating the results, you could look into [FOR XML](https://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/). — Tyler Roper, Oct 20 '16 at 16:28

John Cappelletti · Accepted Answer · 2016-10-20T17:20:31.830

You can use a dynamic conditional aggregation considering you already have Doc#

Declare @SQL varchar(max) = ''
Select @SQL = @SQL+',Desc'+cast([Doc#] as varchar(25))+'=max(case when [Doc#]='+cast([Doc#] as varchar(25))+' then Description else '''' end)'
 From (Select Distinct [Doc#] From YourTable where [Doc#]>1) A 
 Order by [Doc#]

Set @SQL ='
Select [Job#]
      ,[Doc#] = min([Doc#])
      ,Value  = max(Value)
      ,Description = max(case when [Doc#]=1 then Description else '''' end)'+@SQL+'
From YourTable
Group By [Job#]
'
Exec(@SQL)

Returns

Job#    Doc#    Value   Description     Desc2           Desc3
1000    1       100     paint house     clean floors    
1001    1       90      install roof    install boiler  install elevator

@user3062459 Happy to help. – John Cappelletti Oct 20 '16 at 17:44 — John Cappelletti, Oct 20 '16 at 17:44

SQL Server - consolidate data

1 Answers1