T-SQL Pivot

Question

T-SQL Pivot

Triality

SSCommitted

Points: 1958
More actions
March 26, 2015 at 9:56 am

#316539

Hi -
I am trying to pivot my data results so that instead of showing multiple rows for each product a client has it will show one line for each client and concatenate all of their products together.
For example, if I run the following query:
SELECT
[Year],
[Client_Name],
[Product_Name]
FROM My.dbo.Table
GROUP BY
[Year],
[Client_Name],
[Product_Name]
I get the following result set:
YearClient_NameProduct_Name
2014BobHosting
2014BobDevelopment
2014BobTesting
2014FredDevelopment
2014FredConsulting
2014MaxConsulting
2015BobHosting
2015BobTesting
What I want to get back as the result set is the following:
YearClient NameProduct-List
2014BobHosting-Development-Testing
2014FredDevelopment-Consulting
2014MaxConsulting
2015BobHosting-Testing
So, I would only get one record back for each Client for each Year with a list of all of their products concatenated together.
Thanks in advance for any guidance!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

As I understand, you don't want to pivot your rows. You just want a list in a single column. If that's true, try the option explained in here:

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

If you really want to pivot, read the following articles:

Cross tabs and pivots - Part 1[/url]

Cross tabs and pivots - Part 2[/url]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

Had a few moments to kill...

USE tempdb

GO

-- It's always good to provide sample data in an easily usable format like so

CREATE TABLE dbo.

([year] int not null,

Client_Name varchar(100) not null,

Product_Name varchar(100) not null);

INSERT dbo.

VALUES

(2014,'Bob','Hosting'),

(2014,'Bob','Development'),

(2014,'Bob','Testing'),

(2014,'Fred','Development'),

(2014,'Fred','Consulting'),

(2014,'Max','Consulting'),

(2015,'Bob','Hosting'),

(2015,'Bob','Testing');

-- Solution that Luis suggested from http://www.sqlservercentral.com/articles/comma+separated+list/71700/

WITH ClientByYear AS

(

SELECT [year], Client_Name

FROM dbo.

GROUP BY [year], Client_Name

)

SELECT

[year],

Client_Name,

list = STUFF

((

SELECT '-' + Product_Name

FROM dbo.

t

WHERE c.[year] = t.[year] AND c.Client_Name = t.Client_Name

ORDER BY Product_Name

FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')

FROM ClientByYear c;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001