Pivot in sql server: Vertical to Horizontal data

Question

Hi I have the following table that I would like to use the pivot function on:

Id|Number| Code
1 |  34  |abc12345
1 |  23  |xqwe6758
2 |  37  |ghut564hg
3 | 456  |ghut8695
3 |  39  |ghtuj678
3 |  22  |fsdifje12

And I want it to be displayed horizontally as the following:

Id| Code1    | Code2    | Code3
1 | abc12345 | xqwe6758 | null  
2 |ghut564hg | null     | null
3 |ghut8695  | ghtuj678 | fsdifje12


SELECT Id
      ,[Code1]
      ,[Code2]
      ,[Code3]
  FROM(SELECT Id,Code
        FROM [TableName] 
  )d
  pivot(
  max(Id)
  for Code in([Code1],[Code2],[Code3])
  )as piv;

This throws an invalid column name error on the Id column. Could someone help identify the error ?

@DanielCorzo : Since I have very random values in Code column, I don't want to hard-code the "Case" values as mentioned in that link. Would there be a work-around? — user7221204, Dec 07 '16 at 20:46

score 2 · Accepted Answer · edited Feb 25 '17 at 16:27

2

You can use pivot as below:

;with cte as 
(
    select  
        id, code, 
        RowN = Row_Number() over (partition by id order by code) 
    from 
        yourtable1
) 
select * 
from cte
pivot ( max(code) for RowN in([1], [2], [3])) p

For varying columns you can use stuff to create columns list and then use dynamic SQL to run with varying columns... But it is available in various examples in SO itself...

Added my output:

edited Feb 25 '17 at 16:27

marc_s

732,580
175
1,330
1,459

answered Dec 07 '16 at 20:52

Kannan Kandasamy

13,405
3
25
38

It splits up code1 in a line, code 2 in another line instead of one row for ID number 1 – user7221204 Dec 07 '16 at 21:02
That is your required output rite? Can you please clarify required output? – Kannan Kandasamy Dec 07 '16 at 21:04
same line means same row with different columns right? That is what this query gives – Kannan Kandasamy Dec 07 '16 at 21:13
Updated output of mine... If you have any other columns let me know that is causing this problem... – Kannan Kandasamy Dec 07 '16 at 21:19
Can you provide me input samples with your third column as well? – Kannan Kandasamy Dec 07 '16 at 21:29
I have updated my sample input table. I dont want that extra column in the output though – user7221204 Dec 07 '16 at 21:35
How you want to show the number? You want to transpose number column as well? – Kannan Kandasamy Dec 07 '16 at 21:39
If you do not want to transpose the number column then select required columns in CTE as shown in my changed query -- see updated – Kannan Kandasamy Dec 07 '16 at 21:42
Great that works ! Thank you. Could you also please provide an explanation of your query in the answer. – user7221204 Dec 07 '16 at 21:44

score 1 · Answer 2 · answered Dec 07 '16 at 20:56

Try this

DECLARE @tbl TABLE(Id INT, Code VARCHAR(100));
INSERT INTO @tbl VALUES
 (1,'abc12345')
,(1,'xqwe6758')
,(2,'ghut564hg')
,(3,'ghut8695')
,(3,'ghtuj678')
,(3,'fsdifje12');

SELECT p.*
FROM
(
    SELECT Id
           ,'Code' + CAST(ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Code) AS VARCHAR(10)) AS ColumnName
           ,Code
    FROM @tbl
) AS t
PIVOT
(
    MAX(Code) FOR ColumnName IN(Code1,Code2,Code3 /*add as many as you need*/)
) AS p

The result

Id  Code1       Code2       Code3
1   abc12345    xqwe6758    NULL
2   ghut564hg   NULL        NULL
3   fsdifje12   ghtuj678    ghut8695

Pivot in sql server: Vertical to Horizontal data

2 Answers2