Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - PIVOT - two columns into rows

I saw many questions about PIVOT for a single column, each question more complex than other, however, I could not find anything like what I need.

To be honest, I don't even know if pivot will help me in this situation.

Let's say I have this data on 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 of 5 rows with the first name and last name. The value of the columns First and Last will be random.

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

I was trying to pivot this data to something 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 don't have any problem if columns First5 and Last5 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 anyone give me a little help? Thanks.


Solution based on Sheela K R 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
like image 266
navossoc Avatar asked Jan 21 '14 06:01

navossoc


People also ask

Can you PIVOT two columns in SQL?

SQL Pivot Multiple Columns :You can use the SQL Pivot statement to transpose multiple columns.

How do I PIVOT a column into a row in SQL?

Then you apply the aggregate function sum() with the case statement to get the new columns for each color . The inner query with the UNPIVOT performs the same function as the UNION ALL . It takes the list of columns and turns it into rows, the PIVOT then performs the final transformation into columns.

How do I PIVOT multiple columns?

To have multiple columns: Click in one of the cells of your pivot table. Click your right mouse button and select Pivot table Options in the context menu, this will open a form with tabs. Click on the tab Display and tag the check box Classic Pivot table layout.


2 Answers

There are a few different ways that you can get the result that you want. Similar to @Sheela K R'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 could also be written using the PIVOT function, however since you want to pivot multiple columns then you would first want to look at unpivoting your First and Last columns.

The unpivot process will convert your multiple columns into multiple rows of data. You did not specify what version of SQL Server you are using but you can use a SELECT with UNION ALL with CROSS APPLY or even the UNPIVOT function 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 converts your data into the format:

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

Once the data is in multiple rows, then 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 a result of:

|      FIRST1 |       LAST1 |      FIRST2 |       LAST2 |      FIRST3 |       LAST3 |      FIRST4 |       LAST4 |      FIRST5 |       LAST5 |
|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|-------------|
| RandomName1 | RandomLast1 | RandomName2 | RandomLast2 | RandomName3 | RandomLast3 | RandomName4 | RandomLast4 | RandomName5 | RandomLast5 |
like image 94
Taryn Avatar answered Oct 15 '22 18:10

Taryn


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]
like image 35
Sheela K R Avatar answered Oct 15 '22 20:10

Sheela K R