Would pivot work here?

Question

Post reply

Would pivot work here?

GabyYYZ

SSCertifiable

Points: 7913
More actions
November 11, 2008 at 10:27 am

#201095

I have a table that after all is said and done, generates results like:
[font="Courier New"]
User Table
=======================
JDoe Table1
JDoe Table2
JDoe Table3
JSmith Table2
JSmith Table3
PPan Table1
PPan Table3[/font]
What I would like to have is something like:
[font="Courier New"]
User Table(s)
=======================
JDoe Table1, Table2, Table3
JSmith Table2, Table3
PPan Table1, Table3
[/font]
Would a pivot table help or is there a different way to do so?
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

Viewing 11 posts - 1 through 10 (of 10 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

That's not a pivot. It is really string aggregation.

Take a look here for a big list of options:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

Brandie Tarvin SSC Guru Points: 173005 More actions · Answer 2

Gaby, what exactly are you trying to do with the resulting end data?

The way you have it listed, Michael is correct about string aggregation. You could also check into the COALESCE() function. (My new favorite function lately).

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 3

This should get you what you need.

-- create a sample table to play with

DECLARE @SampleTable TABLE

(

UserName VARCHAR(20)

,TableName VARCHAR(20)

)

-- insert some data

INSERT INTO @SampleTable

SELECT 'JDoe', 'Table1'

UNION ALL SELECT 'JDoe', 'Table2'

UNION ALL SELECT 'JDoe', 'Table3'

UNION ALL SELECT 'JSmith', 'Table2'

UNION ALL SELECT 'JSmith', 'Table3'

UNION ALL SELECT 'PPan', 'Table1'

UNION ALL SELECT 'PPan', 'Table3'

-- now for the query

-- use stuff function to create CSV list

SELECT UserName

,STUFF(

(

SELECT ', ' + B.TableName

FROM @SampleTable B

WHERE A.UserName = B.UserName

FOR XML PATH('')

)

,1

, 2

, ''

)

FROM @SampleTable A

GROUP BY UserName

Jeff Moden SSC Guru Points: 1003919 More actions · Answer 4

Ggraber has the right idea... please see the following article for some of the potential pitfalls of concatenation...

http://www.sqlservercentral.com/articles/Test+Data/61572/

And, whatever you do, don't use recurrsion, cursors, or while loops to do this unless you like the word "slow".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brandie Tarvin SSC Guru Points: 173005 More actions · Answer 5

Jeff Moden
Ggraber has the right idea

Possibly, but his code is interupted by the forum turning part of it into a smiley face, which means I get a syntax error when I try to run it. @=)

Ggraber, out of curiosity, why are you using FOR XML PATH in the code?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Jeff Moden SSC Guru Points: 1003919 More actions · Answer 6

Brandie Tarvin (11/12/2008)
Jeff Moden
Ggraber has the right idea
Possibly, but his code is interupted by the forum turning part of it into a smiley face, which means I get a syntax error when I try to run it. @=)
Ggraber, out of curiosity, why are you using FOR XML PATH in the code?

Heh... fix it. It's a right parenthesis. The FOR XML PATH does the concatenation. Stuff get's rid of the first delimiter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Brandie Tarvin SSC Guru Points: 173005 More actions · Answer 7

Aha. Didn't realize it was a paren. Thanks, Jeff. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173005 More actions · Answer 8

Okay, this code is officially going to be my new best friend. @=)

Thanks, Ggraber! Cookies for you!

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 9

I agree with Brandie, sometimes the most useful scripts are surprisingly simple and elegent. Will make my reporting to the Loss Prevention folks more manageable.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 10

Brandie Tarvin (11/12/2008)
Okay, this code is officially going to be my new best friend. @=)
Thanks, Ggraber! Cookies for you!

Yum! I'm hoping chocolate-chip :hehe: