t sql Dynamic Pivot using two columns

Question

DECLARE @Test Table
(
  Name   Varchar(32),
  Code   Varchar(20)
)

INSERT INTO @Test(Name, Code) VALUES
  ('A-1', 'A-One')
, ('A 2', 'A-Two')
, ('B 1-b', 'B-One')
, ('B', 'A-Two')
, ('C', 'A-One')
, ('C', 'B-One')
, ('C', 'C-One')

The sample data set looks like this [again, this is just a small sample]:

Name    Code
A-1     A-One
A 1     A-Two
B 1-b   B-One
B       A-Two 
C       A-One
C       B-One
C       C-One

Notice that Code values [like A-One, A-Two, and B-One] may be associated with more than one Name value.

E.g. A-One appears with Name A-1, as well as Name C ...

I want to output it so it looks like this [except, with a lot more values than I am showing - and those values can change]:

             A-1      A 1        B 1-b          B      C
A-One        X                                         X
A-Two                  X                        X   
B-One                             X                    X
C-One                                                  X

The number of 'Name' values and Code values can change. They are not constant.

The goal is to be able to look down the list of Code values on the left - and easily see which Name values the Codes are associated with.

I believe this requires dynamic pivot sql to be created and I have trouble understanding Pivot sql and I would appreciate any help or pointers.

Seekwell74 · Accepted Answer · 2018-07-26 11:27:34Z

1

With pivot:

select Code, 
case when A > 0 then 'X' else '' end as A, 
case when B > 0 then 'X' else '' end as B, 
case when C > 0 then 'X' else '' end as C
from (
    select Name, Code from @Test
) p
pivot(count(Name) for Name in ([A], [B], [C])) as res;

Dynamic, with Test being a permanent table:

set quoted_identifier off;

declare @caseStmts varchar(max) = '', @inList varchar(max) = '';

select 
    @caseStmts += 
    (case when len(@caseStmts) > 0 then ', ' else '' end) +
    ("case when " + t.Name + " > 0 then 'X' else '' end as " + t.Name + " "),
    @inList += 
    (case when len(@inList) > 0 then ', ' else '' end) +
    ('[' + t.Name + ']')
from (select distinct Name from Test)
as t;

declare @pivotSql nvarchar(max);
select @pivotSql =
    "select Code, " + @caseStmts + 
    " from (select Name, Code from Test) p " +
    "pivot(count(Name) for Name in (" + @inList + ")) as res;";

exec sp_executesql @pivotSql;

And with just single quotes, as well as handling spaces/dashes in Name:

declare @caseStmts varchar(max) = '', @inList varchar(max) = '';

select 
    @caseStmts += 
    (case when len(@caseStmts) > 0 then ', ' else '' end) +
    ('case when [' + t.Name + '] > 0 then ''X'' else '''' end as ''' + t.Name + ''' '),
    @inList += 
    (case when len(@inList) > 0 then ', ' else '' end) +
    ('[' + t.Name + ']')
from (select distinct Name from Test)
as t;

declare @pivotSql nvarchar(max);
select @pivotSql =
    'select Code, ' + @caseStmts + 
    ' from (select Name, Code from Test) p ' +
    'pivot(count(Name) for Name in (' + @inList + ')) as res;';

exec sp_executesql @pivotSql;

edited Jul 26, 2018 at 11:27

answered Jul 24, 2018 at 20:31

Seekwell74

113 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Talay Over a year ago

Thanks @Todd Bellamy. Your code produced the exact output that I wanted for the limited data that I showed. There is no problem with Selecting t.Code because it will get all the t.Code values in the Table. But there are many values of t.Name and they change often. It is not a fixed number of values - plus it changes frequently - so I cannot hard-code each value as you have done in your Case statements. How would I code it to account for all possible values of t.Name that are in the Table at run time?

Talay Over a year ago

Thanks @Todd Bellamy. I will try this and post the results!

Talay Over a year ago

Hi @Todd Bellamy. I am getting an error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'p'." ... and though I am not certain of this, I believe it has to do with the double quotation marks that are being used. I had a similar solution from Shnugo a couple of days ago that worked - but it only used single quotes. Any suggestions?

Seekwell74 Over a year ago

Do you have the 'set quoted_identifier off;' line?

Talay Over a year ago

Yes. I do have the 'set quoted_identifier off;' Is it possible to code this without the double quotes? The only reason I ask is that a similar sql that I used for a different output - uses single quotes only, and that is working fine. Is there another setting in my SQL Server that might be interfering with this sql?

|

Gottfried Lesigang · Accepted Answer · 2018-07-24 20:27:20Z

0

PIVOT will display an existing value (more exact: an aggregated existing value) under a common column name. You might solve this with PIVOT by using CASE statements to return an X and try the pivoting with this value.

But I think, conditional aggregation might be your friend:

DECLARE @Test Table
(
  Name   Varchar(1),
  Code   Varchar(10)
)

INSERT INTO @Test(Name, Code) VALUES
  ('A', 'A-One')
, ('A', 'A-Two')
, ('B', 'B-One')
, ('B', 'A-Two')
, ('C', 'A-One')
, ('C', 'B-One')
, ('C', 'C-One');

SELECT t.Code
      ,MAX(CASE WHEN t.[Name]='A' THEN 'X' END) AS A
      ,MAX(CASE WHEN t.[Name]='B' THEN 'X' END) AS B
      ,MAX(CASE WHEN t.[Name]='C' THEN 'X' END) AS C
      ,MAX(CASE WHEN t.[Name]='D' THEN 'X' END) AS D
FROM @Test t
GROUP BY Code

answered Jul 24, 2018 at 20:27

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

4 Comments

Talay Over a year ago

Thanks @Shnugo. Your code produced the exact output that I wanted for the limited data that I showed. There is no problem with Selecting t.Code because it will get all the t.Code values in the Table. But there are many values of t.Name and they change often. It is not a fixed number of values - plus it changes frequently - so I cannot hard-code each value as you have done. How would I code it to account for all possible values of t.Name that are in the Table at run time?

Gottfried Lesigang Over a year ago

@Talay, you will have to create the whole statement as a string (dynamically created SQL) and use EXEC() to trigger the execution. If you need help with this, please close this question (as the initial question is solved and the above is a completely new issue). Then start a new question with some more sample data and help will rush in immediately.

Talay Over a year ago

Thank you @Shnugo. In my original post, I had this sentence: "The number of 'Name' values and Code values can change. They are not constant." I was hoping that that would make it clear that the limited data I posted was just a small sample? Still, I appreciate your suggestion! Thanks again!!! :D

Talay Over a year ago

Hi @Shnugo. I re-posted at stackoverflow.com/q/51521898/5293055

Collectives™ on Stack Overflow

t sql Dynamic Pivot using two columns

2 Answers 2

12 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related