SQL transform data

Question

I have results coming from a query in the below format

ID CODE
----------
 1   abc
 1   xyz
 1   def
 1   pqr
 1   jkl
 1   tuv

I want the results to be in the following format

ID CODE1 CODE2 CODE3 CODE4 CODE5 CODE6
---------------------------------------
1  abc   xyz   def   pqr   jkl   tuv

I know this can be achieved using static PIVOT query, but the issue I am facing is that the CODE column can have unknown values and I want my column names to be fixed i.e. CODE1, CODE2 and so on 'til CODE6.

Can someone please help me out with this?

Any suggestions are welcome.

Thank you.

Possible duplicate of [How to create a PivotTable in Transact/SQL?](https://stackoverflow.com/questions/11617713/how-to-create-a-pivottable-in-transact-sql) — Nik Shenoy, Apr 26 '18 at 16:24
Personally I find the syntax for Pivot to be very obtuse. I prefer to use a dynamic crosstab for this sort of thing. Here is an example. https://stackoverflow.com/questions/43211349/sql-pivot-possible-for-this-data-set/43212010#43212010 — Sean Lange, Apr 26 '18 at 16:30
@SeanLange You can never go wrong with Moden. And it probably performs a lot better than it looks like it should. And after you remove the tallyTable code, it's not as complex as it looks. I'll have to remember that one. Thanks. — Shawn, Apr 26 '18 at 17:02

score 5 · Accepted Answer · answered Apr 26 '18 at 16:46

5

This can be a fairly simple PIVOT using a ROW_NUMBER() window function. I threw in a 7th CODE for ID 1 to show that it will be ignored if you're only pivoting 6 columns, however, because your OVER isn't deterministic (it can ORDER any way it wants since it only uses ID), it could pick up a different set of 6 CODEs.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE t1 ( ID int, _CODE varchar(20) ) ;
INSERT INTO t1 (ID, _CODE)
VALUES 
    (1,'abc')
  , (1,'xyz')
  , (1,'def')
  , (1,'pqr')
  , (1,'jkl')
  , (1,'tuv')
  , (2,'lmn')
  , (2,'rgb')
  , (1,'ignoredOnly6')
;

Query 1:

SELECT piv.ID
  , piv.[1] AS CODE1
  , piv.[2] AS CODE2
  , piv.[3] AS CODE3
  , piv.[4] AS CODE4 
  , piv.[5] AS CODE5 
  , piv.[6] AS CODE6
FROM (
  SELECT t1.ID, t1._CODE
    , ROW_NUMBER() OVER (PARTITION BY t1.ID ORDER BY t1.ID) AS rn
  FROM t1
) s1
PIVOT (
  max(s1._CODE) 
  FOR s1.rn IN ([1],[2],[3],[4],[5],[6]) 
) piv

Results:

| ID | CODE1 | CODE2 |  CODE3 |  CODE4 |  CODE5 |  CODE6 |
|----|-------|-------|--------|--------|--------|--------|
|  1 |   abc |   xyz |    def |    pqr |    jkl |    tuv |
|  2 |   lmn |   rgb | (null) | (null) | (null) | (null) |

answered Apr 26 '18 at 16:46

Shawn

4,758
1
20
29

NOTE: I also used table aliases so you can see where the values are coming from in the query. I will agree with Sean, `PIVOT`s can get a bit wonky. – Shawn Apr 26 '18 at 17:04
Thank you so much for your answer.Much appreciated. – Priyank Patel Apr 26 '18 at 17:27
My over actually has 3 columns so will this query change by any way? – Priyank Patel Apr 26 '18 at 17:31
How do you mean? You can have multiple columns that don't get pivoted in the `s1` subquery. That will change how your window is partitioned (`rn`). – Shawn Apr 26 '18 at 17:40
Yes I have total 4 columns in my sub query, out of which code is pivoted. – Priyank Patel Apr 26 '18 at 17:46
It depends on how those columns relate to each other. They'll all need to be in the outer (and inner) `SELECT`. You can write out the query in `s1` to pull the data that you want. The `ROW_NUMBER()` is supposed to give you a number 1-6 to `PIVOT` in the outer query. – Shawn Apr 26 '18 at 17:53

SQL transform data

1 Answers1