3

This is a small sample of the data. In the actual data, the values under Name and Code are in the hundreds and those values change frequently. For this reason, hard-coding the Pivot statement will not work. There needs to be a dynamic SQL statement created - and I need help with that.

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.

Improve this question
7
  • Good day Talay, Try to work by these steps. present what you succeed and we will add the missing steps so you will understand the idea for future cases as well. (1) Create a query that returns a list of all the distinct names in your table. (2) declare a variable type NVARCHAR(MAX). (3) CONCAT all these values from step 1 into your variable from step 2. (4) create simple pivot query using specific list of names - not yet a dynamic query... if you did all these steps well then you only need to combine all together, and i came to the limitation of the message length :-) show steps 1-4 Commented Jul 25, 2018 at 16:49
  • Thank you so much @Ronen Ariely! I appreciate this more than a ready-made solution because you are educating me. I will follow your 4 steps and post back. Commented Jul 25, 2018 at 17:13
  • You are most welcome. Like the phrase says "Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime". Unfortunately most people race for points and not to teach, and posting the final query is much faster and simpler than going step by step and following the thread for long time. My interest is only in helping those who want to learn how to do it themselves, and not to work for free and provide final query if you cannot learn from it :-) Commented Jul 25, 2018 at 17:34
  • Well... It seems like the lesson was ended too soon ;-( since Andrea published the final step. So you can mark his answer and give him the points. He deserves to get the points for posting the final query. With that being said, I recommend to continue working by the steps I gave you, and if you want I will explain the last step: how we combine all the information from steps 1-4 into final query :-) Commented Jul 25, 2018 at 20:04
  • Hi @Ronen Ariely. I had followed your 4 steps. and I was getting an idea of what would be needed in the final step [instead of the hard-coded values in the Pivot statement, I would have to dynamically create a string based on all distinct Names]. I think that part will be difficult for me but I am determined to understand the whole thing if you don't mind helping. Commented Jul 25, 2018 at 20:41

1 Answer 1

Reset to default
1

You can use a dynamic pivot:

create table dbo.test([Name] Varchar(32), Code Varchar(30)) 

insert into dbo.test 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')

declare @cols nvarchar(max)=''  --holds all the values that will become column names 
declare @alias nvarchar(max)='' --holds values that will become column names and converts values to 'x'
declare @sql nvarchar(max)=''   --contains the TSQL dinamically generated 

select @cols = @cols + ', [' + [Name] + ']' 
from dbo.test 
group by [Name] 

select @alias = @alias + ', case when T.[' + [Name] + '] is not null then ''x'' else '''' end as [' + [Name] + ']'
from dbo.test 
group by [Name] 

set @cols = RIGHT(@cols, len(@cols)-2) 
set @sql = @sql + ' select  T.Code ' + @alias + ' from ('
set @sql = @sql + ' select piv.[Code], ' + @cols 
set @sql = @sql + ' from dbo.test ' 
set @sql = @sql + ' pivot (max([Name]) for [Name] in ('+@cols+') ) piv ' 
set @sql = @sql + ' ) T' 

--execute dynamic query
exec(@sql)

Results:

enter image description here

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

4 Comments

Hi @Andrea. Your code works with the given data. Thanks. However, the actual data I am working with has hyphens and spaces in the Name field. The sql fails when it encounters spaces or hyphens in the Name field. Spaces and/or hyphens in the Code field do not cause the error. How would I fix that?
you will need to provide sample data for better understanding and to prevent us from guessing, but in first glance and as first guess it seems like the issue in Andre's solution is in the @alias. You need to make a tiny fix to the code. Anyhow, if we will go step by step then you will learn how to check the cpde before execute it and you will notice the issue immediately :-) . You can use PRINT @alias to understand what is the issue,
Hi! I have edited the original post so that now there is data in the sample that should throw the error I am getting. Thanks!!
@Talay Just add square brackets around column names. See updated answer.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.