asked
Please suggest way to convert group of row values to columns
Please see the picture for current result set vs required result set structure. Also attached the excel with data, note I have thousands of rows of such data. ![alt text][1] [link text][2] [1]: /storage/temp/4604-2018-04-19-12-50-51.png [2]: /storage/temp/4603-sqlcentralquestion-grouptocolumn.xlsx
@SoulSeeker Do you have SQL Server or something else? If it is SQL Server then is it newer than SQL Server 2000? Please clarify. Also, do you know in advance about how many AccountId values can be present for a single DUNS, Owner combination? The maximum number of rows per combination is of interest. Is it known or not? If not then the solution will have to be via technique known as dynamic pivot. If it is known then the solution is much simpler. The reason it is important is because it determines the number of columns in the final result. Say, you have most combinations including a handful of rows for each DUNS, Owner combination, but there is one combination which has, say, 20 rows. This means that you must include 20 columns in your final result even though the values in the pivoted columns will be blanks for most DUNS, Owner combinations except for one which does have all 20.
@ oleg, i do have SQL server 2014. And the number of ids for each DUNS is not known, it can be anything but more than 1. Thanks a ton.
I will assume that there is a table, let's call it YourTable, which has 3 columns: DUNS, [Owner], and AccountIds. For each DUNS, [Owner] combination there may be multiple rows, each with different AccountIds value, and the number of such rows is unknown. Should such maximum number of rows (which determines the number of pivoted columns) be known, the standard static script could be used to pivot the data. Let's say that we know that the number is 5. Then the following select may be used: -- static pivot, this may be used if the number of pivoted columns is known select * from ( select DUNS, [Owner], AccountIds, row_number() over (partition by DUNS, [Owner] order by AccountIds) n from YourTable ) src pivot (max(AccountIds) for n in ([1], [2], [3], [4], [5])) pvt; It is easy to see that the only part of the script which needs to change is the part listing the numbers. So, in order to generate the script which will work for any number of pivoted columns, something like this may be used: -- dynamic script, needs to be used if the number of columns is not known declare @columnList nvarchar(max) = stuff(( select top (select top 1 count(1) from YourTable group by DUNS, [Owner] order by 1 desc) ', ' + quotename(row_number() over (order by (select null))) from YourTable for xml path('') ), 1, 2, ''); -- print @columnList; declare @sql nvarchar(max) = ' select * from ( select DUNS, [Owner], AccountIds, row_number() over (partition by DUNS, [Owner] order by AccountIds) n from YourTable ) src pivot (max(AccountIds) for n in (' + @columnList + ')) pvt; '; -- print @sql; exec sp_executesql @statement = @sql; go Please uncomment the commented out print statements to see what is being generated. Based on the sample data in question, this dynamic pivot script produces the following results: DUNS Owner 1 2 3 ----------- ----- ---- ---- ---- 1 a s t u 2 b v w NULL 3 c x y NULL Hope this helps. Oleg
19 People are following this question.
