SQL Server - PIVOT - two columns per row

Question

I saw many PIVOT questions for one column, each of which was more complex than the others, however I could not find anything similar to what I needed.

Honestly, I don’t even know if the summary information will help me in this situation.

Let's say I have this data in my source table:

SELECT '1' as 'RowId', 'RandomName1' as 'First', 'RandomLast1' as 'Last' UNION SELECT '2' as 'RowId', 'RandomName2' as 'First', 'RandomLast2' as 'Last' UNION SELECT '3' as 'RowId', 'RandomName3' as 'First', 'RandomLast3' as 'Last' UNION SELECT '4' as 'RowId', 'RandomName4' as 'First', 'RandomLast4' as 'Last' UNION SELECT '5' as 'RowId', 'RandomName5' as 'First', 'RandomLast5' as 'Last' 

Maximum 5 lines with first and last name. The value of the First and Last columns will be random.

 RowId First Last ----- ----------- ----------- 1 RandomName1 RandomLast1 2 RandomName2 RandomLast2 3 RandomName3 RandomLast3 4 RandomName4 RandomLast4 5 RandomName5 RandomLast5 

I tried to collapse this data like this:

 First1 Last1 First2 Last2 First3 Last3 First4 Last4 First5 Last5 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- RandomName1 RandomLast1 RandomName2 RandomLast2 RandomName3 RandomLast3 RandomName4 RandomLast4 RandomName5 RandomLast5 

For example: I have no problem if the First5 and Last5 columns are NULL because there are only 4 rows.

 First1 Last1 First2 Last2 First3 Last3 First4 Last4 First5 Last5 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- RandomName1 RandomLast1 RandomName2 RandomLast2 RandomName3 RandomLast3 RandomName4 RandomLast4 NULL NULL 

Can anybody help me? Thanks.

---

A solution based on Sheela KR answers:

 SELECT MAX(First1) as 'First1', MAX(Last1) as 'Last1', MAX(First2) as 'First2', MAX(Last2) as 'Last2', MAX(First3) as 'First3', MAX(Last3) as 'Last3', MAX(First4) as 'First4', MAX(Last4) as 'Last4', MAX(First5) as 'First5', MAX(Last5) as 'Last5' FROM ( SELECT CASE WHEN RowId = 1 THEN [First] END as 'First1', CASE WHEN RowId = 1 THEN [Last] END as 'Last1', CASE WHEN RowId = 2 THEN [First] END as 'First2', CASE WHEN RowId = 2 THEN [Last] END as 'Last2', CASE WHEN RowId = 3 THEN [First] END as 'First3', CASE WHEN RowId = 3 THEN [Last] END as 'Last3', CASE WHEN RowId = 4 THEN [First] END as 'First4', CASE WHEN RowId = 4 THEN [Last] END as 'Last4', CASE WHEN RowId = 5 THEN [First] END as 'First5', CASE WHEN RowId = 5 THEN [Last] END as 'Last5' FROM ( SELECT '1' as 'RowId', 'RandomName1' as 'First', 'RandomLast1' as 'Last' UNION SELECT '2' as 'RowId', 'RandomName2' as 'First', 'RandomLast2' as 'Last' UNION SELECT '3' as 'RowId', 'RandomName3' as 'First', 'RandomLast3' as 'Last' UNION SELECT '4' as 'RowId', 'RandomName4' as 'First', 'RandomLast4' as 'Last' --UNION SELECT '5' as 'RowId', 'RandomName5' as 'First', 'RandomLast5' as 'Last' ) test ) test2 

Answer 1

There are several different ways to get the result you want. As in @Sheela KR's answer, you can use an aggregate function with a CASE expression, but it can be written in a more concise way:

 select max(case when rowid = 1 then first end) First1, max(case when rowid = 1 then last end) Last1, max(case when rowid = 2 then first end) First2, max(case when rowid = 2 then last end) Last2, max(case when rowid = 3 then first end) First3, max(case when rowid = 3 then last end) Last3, max(case when rowid = 4 then first end) First4, max(case when rowid = 4 then last end) Last4, max(case when rowid = 5 then first end) First5, max(case when rowid = 5 then last end) Last5 from yourtable; 

See SQL Fiddle with Demo .

This can also be recorded using the PIVOT function, however, since you want to expand multiple columns, you first need to take a look at the promotion of the First and Last columns.

A non-repetitive process converts your multiple columns into multiple rows of data. You did not specify which version of SQL Server you are using, but you can use SELECT with UNION ALL using the CROSS APPLY function or even UNPIVOT to perform the first conversion:

 select col = col + cast(rowid as varchar(10)), value from yourtable cross apply ( select 'First', First union all select 'Last', Last ) c (col, value) 

See SQL Fiddle with Demo . This will convert your data to the format:

 | COL | VALUE | |--------|-------------| | First1 | RandomName1 | | Last1 | RandomLast1 | | First2 | RandomName2 | | Last2 | RandomLast2 | 

Once the data is in several rows, you can easily apply the PIVOT function:

 select First1, Last1, First2, Last2, First3, Last3, First4, Last4, First5, Last5 from ( select col = col + cast(rowid as varchar(10)), value from yourtable cross apply ( select 'First', First union all select 'Last', Last ) c (col, value) ) d pivot ( max(value) for col in (First1, Last1, First2, Last2, First3, Last3, First4, Last4, First5, Last5) ) piv; 

See SQL Fiddle with Demo

Both give the result:

 | FIRST1 | LAST1 | FIRST2 | LAST2 | FIRST3 | LAST3 | FIRST4 | LAST4 | FIRST5 | LAST5 | |-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------| | RandomName1 | RandomLast1 | RandomName2 | RandomLast2 | RandomName3 | RandomLast3 | RandomName4 | RandomLast4 | RandomName5 | RandomLast5 | 

Answer 2

Try something like this

 CREATE TABLE #Table1 ([uid] int, [name] varchar(4), [diseaseid] int, [intensity] varchar(4)) ; INSERT INTO #Table1 ([uid], [name], [diseaseid], [intensity]) VALUES (1, 'xxxx', 2, 'low') (1, 'xxxx', 1, 'high'), ; SELECT MAX([uid]) AS [uid] ,MAX([name]) AS [name] ,MAX([diseaseid1]) AS [diseaseid1] ,MAX([intensity1]) AS [intensity1] ,MAX([diseaseid2]) AS [diseaseid2] ,MAX([intensity2]) [intensity2] FROM ( SELECT [uid], [name] , CASE WHEN rn=2 THEN NULL ELSE [diseaseid] END AS [diseaseid1] , CASE WHEN rn=2 THEN NULL ELSE [intensity] END AS [intensity1] , CASE WHEN rn=1 THEN NULL ELSE [diseaseid] END AS [diseaseid2] , CASE WHEN rn=1 THEN NULL ELSE [intensity] END AS [intensity2] FROM ( SELECT [uid], [name], [diseaseid], [intensity], ROW_NUMBER() OVER(PARTITION BY [uid] ORDER BY Name) AS rn FROM #Table1 ) T ) T GROUP BY [uid], [name]