Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

CTE Expand / Collapse
Author
Message
Posted Wednesday, April 14, 2010 1:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746
Also, your code won't work as is when run on a system using a case sensitive collation.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #903501
Posted Wednesday, April 14, 2010 1:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746
Here is my code:

select distinct
reportID,
RoleID,
stuff((select ', ' + t2.User2
from #tbl t2
where t2.reportID = t1.reportID and t2.RoleID = t1.RoleID
for xml path('')),1,2,'') as List
from #tbl t1;




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #903505
Posted Wednesday, April 14, 2010 1:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746
ychen 55059 (4/14/2010)
Would the 'into #tbl ' above suffice? I wanted to be consistent with the #tbl referenced in the CTE query.


It works, just not the way you should do it. It would be better to separate your DDL from the load of your data.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #903512
Posted Wednesday, April 14, 2010 3:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 24, 2011 12:39 PM
Points: 9, Visits: 36
Thank you so much. That works beautifully.

I'm not familiar with XML. Could you point me to some additional resources so that I can understand what your code actually does?

Thanks again.
Post #903625
Posted Wednesday, April 14, 2010 3:28 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746
ychen 55059 (4/14/2010)
Thank you so much. That works beautifully.

I'm not familiar with XML. Could you point me to some additional resources so that I can understand what your code actually does?

Thanks again.


Not that familiar myself, just something I picked up here on SSC. Hopefully someone else reading this thread can provide you with a link. I'd have to do some searching and I am getting ready to leave to watch (yes watch, not ref) a soccer game.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #903628
Posted Wednesday, April 14, 2010 3:30 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
ychen 55059 (4/14/2010)
Thank you so much. That works beautifully.

I'm not familiar with XML. Could you point me to some additional resources so that I can understand what your code actually does?

Thanks again.


You can start here.
http://msdn.microsoft.com/en-us/library/ms190922.aspx




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #903629
Posted Wednesday, April 14, 2010 3:32 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
Here is a SQLMagazine article on the topic.
http://www.sqlmag.com/article/tsql3/nifty-ways-to-use-for-xml-path-concatenation.aspx




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #903631
Posted Wednesday, April 14, 2010 3:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 24, 2011 12:39 PM
Points: 9, Visits: 36
THANK YOU!!
Post #903634
Posted Wednesday, April 14, 2010 3:37 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 17,977, Visits: 15,981
You're welcome.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #903635
Posted Wednesday, April 14, 2010 4:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 6,842, Visits: 13,370
Maybe this link will be of some help, too:
http://www.sqlservercentral.com/Forums/Topic679572-338-2.aspx
Barry did a great job explaining how the STUFF ... FOR XML PATH actually works.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #903671
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse