Group by the first column, and split (pivot?), The remaining two columns

Question

The TSQL question is here. See Source and desired result in the image below. Code is also provided to create the source table.

---

DECLARE @tablevar TABLE( record nvarchar(10), category nvarchar(50), value float) INSERT INTO @tablevar VALUES ('110-AL','credits_cle',1), ('110-AL','credits_ethics',2), ('110-AR','credits_ethics',2.5), ('110-AZ','credits_prof_resp',1.5), ('110-AZ', 'credits_ethics',5), ('110-AZ', 'credits_cle',4) 

Answer 1

Since you want PIVOT two columns of data, one way to do this is to use the UNPIVOT and PIVOT functions. UNPIVOT converts several category and value columns into several rows, then you can apply PIVOT to get the final result:

 select record, category1, value1, category2, value2, category3, value3 from ( select record, col+cast(seq as varchar(10)) col, val from ( select record, category, cast(value as nvarchar(50)) value, row_number() over(partition by record order by category) seq from tablevar ) d unpivot ( val for col in (category, value) ) unpiv ) src pivot ( max(val) for col in (category1, value1, category2, value2, category3, value3) ) piv; 

See SQL Fiddle with Demo .

If you have an unknown number of values, you will have to use dynamic SQL similar to this:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(seq as varchar(10))) from ( select row_number() over(partition by record order by category) seq from tablevar ) d cross apply ( select 'category', 1 union all select 'value', 2 ) c (col, so) group by seq, so, col order by seq, so FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT record,' + @cols + ' from ( select record, col+cast(seq as varchar(10)) col, val from ( select record, category, cast(value as nvarchar(50)) value, row_number() over(partition by record order by category) seq from tablevar ) d unpivot ( val for col in (category, value) ) unpiv ) x pivot ( max(val) for col in (' + @cols + ') ) p ' execute(@query); 

See SQL Fiddle with Demo

Answer 2

This is the first time I've used PIVOT, the code is probably pretty ugly. Here:

 with ranked as ( select *, RANK() OVER (PARTITION by record ORDER by category) as r from @tablevar ), labeled as ( select record, category as content, 'category' + CAST(r as varchar(MAX)) as label from ranked union all select record, cast(value AS nvarchar(MAX)), 'value' + CAST(r as varchar(MAX)) as label from ranked) --select * from labeled select record, [category1] as [category], [value1] as [value], [category2] as [category], [value2] as [value], [category3] as [category], [value3] as [value] from (SELECT * FROM labeled) as source PIVOT( max(content) for label in ([category1], [value1], [category2], [value2], [category3], [value3])) as pvt 

Answer 3

Here is my punch in it

 ;with Z as ( select record, category, value, ROW_NUMBER() over (partition by record order by category) as ranker from @tablevar ) select Z2.record, Z2.c1, Z3.v1, Z2.c2, Z3.v2, Z2.c3, Z3.v3 from ( select record, [1] c1, [2] c2, [3] c3 from (select record, category, ranker from Z) as Z0 pivot ( min(category) for ranker in ([1], [2], [3])) as pvt ) Z2 join ( select record, [1] v1, [2] v2, [3] v3 from (select record, value, ranker from Z) as Z1 pivot ( min(value) for ranker in ([1], [2], [3])) as pvt ) Z3 on Z2.record = Z3.record