T-SQL - Pivot/Crosstab - variable number of values

Question

I have a simple data set that looks like this:

Name    Code
A       A-One
A       A-Two
B       B-One
C       C-One
C       C-Two
C       C-Three

I want to output it so it looks like this:

Name    Code1    Code2    Code3    Code4    Code...n ...
A       A-One    A-Two
B       B-One
C       C-One    C-Two    C-Three

For each of the 'Name' values, there can be an undetermined number of 'Code' values.

I have been looking at various examples of Pivot SQL [including simple Pivot sql and sql using the XML function?] but I have not been able to figure this out - or to understand if it is even possible.

I would appreciate any help or pointers.

Thanks!

Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — JNevill, Jul 23 '18 at 14:40
Thanks @JNevill. I will try this out with my data and see if it gives me what I am looking for. Appreciate it. — Talay, Jul 23 '18 at 15:26

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Try it like this:

DECLARE @tbl TABLE([Name] VARCHAR(100),Code VARCHAR(100));
INSERT INTO @tbl VALUES
 ('A','A-One')
,('A','A-Two')
,('B','B-One')
,('C','C-One')
,('C','C-Two')
,('C','C-Three');

SELECT p.*
FROM 
(
    SELECT * 
          ,CONCAT('Code',ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY Code)) AS ColumnName
    FROM @tbl 
)t
PIVOT
(
    MAX(Code) FOR ColumnName IN (Code1,Code2,Code3,Code4,Code5 /*add as many as you need*/)
)p;

This line

,CONCAT('Code',ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY Code)) AS ColumnName

will use a partitioned ROW_NUMBER in order to create numbered column names per code. The rest is simple PIVOT...

UPDATE: A dynamic approach to reflect the max amount of codes per group

CREATE TABLE TblTest([Name] VARCHAR(100),Code VARCHAR(100));
INSERT INTO TblTest VALUES
 ('A','A-One')
,('A','A-Two')
,('B','B-One')
,('C','C-One')
,('C','C-Two')
,('C','C-Three');

DECLARE @cols VARCHAR(MAX);
WITH GetMaxCount(mc) AS(SELECT TOP 1 COUNT([Code]) FROM TblTest GROUP BY [Name] ORDER BY COUNT([Code]) DESC)
SELECT @cols=STUFF(
(
    SELECT CONCAT(',Code',Nmbr)
    FROM
    (SELECT TOP((SELECT mc FROM GetMaxCount)) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) t(Nmbr)
    FOR XML PATH('')
),1,1,'');

DECLARE @sql VARCHAR(MAX)=
'SELECT p.*
FROM 
(
    SELECT * 
          ,CONCAT(''Code'',ROW_NUMBER() OVER(PARTITION BY [Name] ORDER BY Code)) AS ColumnName
    FROM TblTest 
)t
PIVOT
(
    MAX(Code) FOR ColumnName IN (' +  @cols + ')
)p;';

EXEC(@sql);
GO

DROP TABLE TblTest;

As you can see, the only part which will change in order to reflect the actual amount of columns is the list in PIVOTs IN() clause.

You can create a string, which looks like Code1,Code2,Code3,...CodeN and build the statement dynamically. This can be triggered with EXEC().

I'd prefer the first approach. Dynamically created SQL is very mighty, but can be a pain in the neck too...

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 23 '18 at 14:55

Shnugo

66,100
9
53
114

Thanks, @Shnugo. I appreciate this response. The problem with hard-coding the "Code1, Code2, Code3 ..." is that today there may be 50 Codes in some groups. Next week it could go up to 80 for the same - or different groups. I am looking for a way of doing this in tsql that handles for the max number of different Codes in any group. – Talay Jul 23 '18 at 15:25
@Talay, you can add the possible maximum of columns and call this with a dynamically created `SELECT` with a dynamically created list of column names. You can create the statement above dynamically too. Might be, this is the XY-problem... Try to explain what you are trying to achieve... – Shnugo Jul 23 '18 at 15:29
Your Code perfectly solved the issue with the limited data I originally posted. Thank you for that. I don't understand your second response about dynamically created list of columns names and dynamically created statement - but I believe that is what I am looking for. To explain: I want the sql to be able to determine how many Code-numbered columns are required and put them across the top of the output. Then, each Name will have its different Codes listed out under as many Code# columns are required for that Code. Is that clearer? – Talay Jul 23 '18 at 16:03
Thanks again. I will try it and post the results. Appreciate it!! – Talay Jul 23 '18 at 16:09
@Talay, Try the part of my update which fills `@cols` and check out `SELECT @cols`. This is the dynamically created column list. You can create a dynamic SQL with `'SELECT ' + @cols + ' FROM ...` and let this read from the first part of my answer wrapped as `VIEW`... – Shnugo Jul 23 '18 at 16:17
Many Thanks @Shnugo. Your dynamic code update to your original suggestion solved my problem perfectly. Greatly appreciated!!! – Talay Jul 23 '18 at 16:41
Sorry to bother you @Shnugo ... is it possible to do an IsNull (Code, '') As Code for the Code column so that instead of 'NULL' there is an empty string where there are no values? – Talay Jul 23 '18 at 17:05
Hi again @Shnugo. I noticed another thing when I was examining my actual output. For some of the Name rows, NULL values appear before actual values. For other Name rows, actual values appear first - followed by NULL values. I find the code complex and I have not been able to fix this anomaly. Can you help, please? Or should I create a separate question for this? – Talay Jul 23 '18 at 17:27
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/176588/discussion-between-talay-and-shnugo). – Talay Jul 23 '18 at 18:01
I believe I found the problem. The data in my actual Insert statement had NULL values and Empty Strings in the Code field. Once I took those out, the data presents just fine in the output. Sorry for the bother & Thanks again!! – Talay Jul 23 '18 at 19:10

T-SQL - Pivot/Crosstab - variable number of values

1 Answers1

UPDATE: A dynamic approach to reflect the max amount of codes per group