simple SQL Pivot Query

Question

I apologize for my ignorance. I just am not familiar with pivot queries AT ALL and all the examples I find seem about as clear as mud. I have table that returns GroupName and ID Numbers.

For Example:

SELECT GroupName, IDnumber FROM do.Table_1

Returns

GroupName                IDnumber
1                          8395
1                          A660
1                          8396
1                          A661
2                          8398
2                          A662
2                          8399

What I want is something more like this:

GroupName              ID1     ID2   ID3   ID4
1                      8395   A660  8396   A661
2                      8398   A662  8399   NULL

How can I do this? Pivot query? Some other method?

I am open to suggestion and appreciate any help you could provide.

Mahmoud Gamal · Accepted Answer · 2014-12-11T14:59:38.413

Yes, you can do it using PIVOT but not in this shape, you have firstly to generate a row number to use it to format the data in the way you want. Something like this:

WITH Ranked
AS
(
  SELECT GroupName, IDnumber, 
     ROW_NUMBER() OVER(PARTITION BY GroupName ORDER BY GroupName) AS RN
  FROM Table1
)
SELECT GroupName,
  [1] AS ID1, [2] AS ID2, [3] AS ID3, [4] AS ID4
FROM Ranked AS r
PIVOT
(
  MAX(IDnumber)
  FOR RN IN([1], [2], [3], [4])
) AS p;

SQL Fiddle Demo

This will give you:

| GROUPNAME |  ID1 |  ID2 |  ID3 |    ID4 |
|-----------|------|------|------|--------|
|         1 | 8395 | A660 | 8396 |   A661 |
|         2 | 8398 | A662 | 8399 | (null) |

If you want to do it dynamically and not to write the row number by hand in the pivot table operator, you have to do it using dynamic SQL, something like:

DECLARE @cols AS NVARCHAR(MAX);
DECLARE @colnames AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(RN)
                       FROM 
               (
                 SELECT ROW_NUMBER() OVER(PARTITION BY GroupName ORDER BY GroupName) AS RN
                 FROM Table1 
               ) AS t
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @colnames = STUFF((SELECT distinct ',' +
                        QUOTENAME(RN) + 'AS' +   
                        QUOTENAME('ID' + CAST(RN AS NVARCHAR(5)))
                       FROM 
               (
                 SELECT ROW_NUMBER() OVER(PARTITION BY GroupName ORDER BY GroupName) AS RN
                 FROM Table1 
               ) AS t
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '');

SELECT @query = 'WITH Ranked
                  AS
                  (
                    SELECT GroupName, IDnumber, 
                    ROW_NUMBER() OVER(PARTITION BY GroupName ORDER BY GroupName) AS RN
                    FROM Table1
                  )
                  SELECT GroupName, ' + @colnames +
                  ' FROM Ranked AS r
                  PIVOT
                  (
                    MAX(IDnumber)
                    FOR RN IN(' + @cols + ')' +
                  ') p';

execute(@query);

SQL Fiddle Demo

This should give you the same result:

| GROUPNAME |  ID1 |  ID2 |  ID3 |    ID4 |
|-----------|------|------|------|--------|
|         1 | 8395 | A660 | 8396 |   A661 |
|         2 | 8398 | A662 | 8399 | (null) |

Ok, This works fantastic! It does exactly what I want to do. But I can't save it. When I try to save the non-dynamic version as a view so I can reuse it for my application, it cuts the first three lines off. Can't figure out why. If I try to save the dynamic version it says it has a problem with the DECLARE statement and won't let me save it. Thanks for the help. — Jeff, Dec 12 '14 at 14:00
@Jeff - You can't use it in a view. You can do it in a stored procedure instead. It should work fine. — Mahmoud Gamal, Dec 14 '14 at 10:20
Ok, I got it figured out and working in my application. Thank you! — Jeff, Dec 15 '14 at 13:39
I even went this one better and figured out how to do it with a parameter passed to it from a query string! It's working perfectly. Thank you! — Jeff, Dec 16 '14 at 14:09

Sarath Subramanian · Answer 2 · 2014-12-12T09:08:42.147

You may need to use dynamic pivoting since the Id will be dynamic. Here is your sample table

SELECT * INTO #TEMP
FROM 
(
SELECT 1 GroupName,                '8395' IDnumber
  UNION ALL
SELECT 1,                          'A660'
  UNION ALL
SELECT 1,                          '8396'
  UNION ALL
SELECT 1,                          'A661'
  UNION ALL
SELECT 2,                          '8398'
  UNION ALL
SELECT 2,                          'A662'
  UNION ALL
SELECT 2,                          '8399'
)TAB

Select row number over each Groupname and insert into a temporary table so that it can be used for both selecting the columns for pivoting and inside the pivot

SELECT *,
'ID' + CAST(ROW_NUMBER() OVER(PARTITION BY GroupName ORDER BY GROUPNAME) AS VARCHAR(10)) IDS
INTO #NEWTABLE
FROM #TEMP

Select columns for pivot

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + IDS + ']', 
           '[' + IDS + ']')
           FROM    (SELECT DISTINCT IDS FROM #NEWTABLE) PV  
           ORDER BY IDS

Now pivot dynamically

DECLARE @query NVARCHAR(MAX)
SET @query = '           
              SELECT * FROM 
             (
                 SELECT * FROM #NEWTABLE
             ) x
             PIVOT 
             (
                 MAX(IDnumber)
                 FOR IDS IN (' + @cols + ')
            ) p      

            '     
EXEC SP_EXECUTESQL @query

Click here to view the result (incase an error is occured on loading page press RUNSQL, it works)

RESULT

enter image description here

I have updated. Please check. Its simple to read and understand @Jeff — Sarath Subramanian, Dec 12 '14 at 09:17

simple SQL Pivot Query

2 Answers2

Linked