FLATTEN OUT ROW BASED STRUCTURE TO COMMA DELIMITED

Question

Post reply

FLATTEN OUT ROW BASED STRUCTURE TO COMMA DELIMITED

BaldingLoopMan

SSCertifiable

Points: 6378
More actions
November 8, 2010 at 12:58 pm

#242695

Anyone know how to do the following. Trying to take records from the table and create a delimited string of vaules by attribute...

create table #tt

(attribute_id int

, Val varchar(50)

)

insert into #tt

values(1,'value1')

insert into #tt

values(1,'value2')

insert into #tt

values(2,'value1')

insert into #tt

values(2,'value3')

insert into #tt

values(3,'value1')

insert into #tt

values(3,'value2')

insert into #tt

values(3,'value5')

insert into #tt

values(4,'value2')

SELECT *

FROM #tt

----RESULTS:

--1,'Value1, Value2'

--2,'Value1, ValueC'

--3,'Value1, Value2,Value5'

--1,'Value2'

DROP TABLE #tt

Viewing 14 posts - 1 through 13 (of 13 total)

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

Snargables SSCrazy Eights Points: 8683 More actions · Answer 1

Snargables

SSCrazy Eights

Points: 8683

November 8, 2010 at 1:47 pm

#1247868

easy now. Not everyone at once

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

BaldingLoopMan (11/8/2010)

----RESULTS:

--1,'Value1, Value2'

--2,'Value1, ValueC'

--3,'Value1, Value2,Value5'

--1,'Value2'

Need a results confirmation here, just making sure it's user error not expectation:

ValueC came from where?

There's no space between the second and third value in line 3. on purpose?

Why is the fourth line also row 1?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

WayneS SSC Guru Points: 95405 More actions · Answer 3

SELECT DISTINCT



       attribute_id,

       Val = stuff((SELECT ', ' + Val

                      FROM #tt

                     WHERE attribute_id = t1.attribute_id

                     ORDER BY attribute_id, Val

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

  FROM #tt t1;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

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

captcooldaddy (11/8/2010)

easy now. Not everyone at once

BWAA-HAAA!!!! Let's see what YOU have on this one. 😉

--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)
Intro to Tally Tables and Functions

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 5

My deepest apologies Jeff. I told someone he was as useful as a paraplegic mime in a past and that didn’t go over so well. So I was under the impression I was being boycotted. This was my second post w/o getting any feedback as well. This one never actually got an answer. http://qa.sqlservercentral.com/Forums/Topic1016631-392-1.aspx. So I overreacted and jostled the hornets’ nest a bit. To make it worse, someone was actually in the process of replying when I sent the “easy now. Not everyone at once” and I never even checked it again. None the less I stand corrected and am thankful for the above assistance. Thanks. Now i just need to look this over and consume it. For some reason simply putting the “for xml” makes my brain not want to learn it.

LutzM SSC Guru Points: 107049 More actions · Answer 6

BaldingLoopMan (11/18/2010)

My deepest apologies Jeff. I told someone he was as useful as a paraplegic mime in a past and that didn’t go over so well. So I was under the impression I was being boycotted. This was my second post w/o getting any feedback as well. This one never actually got an answer. http://qa.sqlservercentral.com/Forums/Topic1016631-392-1.aspx. So I overreacted and jostled the hornets’ nest a bit. To make it worse, someone was actually in the process of replying when I sent the “easy now. Not everyone at once” and I never even checked it again. None the less I stand corrected and am thankful for the above assistance. Thanks. Now i just need to look this over and consume it. For some reason simply putting the “for xml” makes my brain not want to learn it.

If you feel the need to use a second nickname to post something you should know that you shouldn't hit the "post" button.

It's common to use a "BUMP" post if you feel the need to bring it up the list. But you shouldn't use it within less than one hour...

It seems like you're expecting us to sit at our keyboards doing nothing else than waiting for your post to answer. You might want to change that expectation.

In case of an emergency, call 911, not SSC!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 7

-- you feel the need to use a second nickname to post something you should know that you shouldn't hit the "post" button.

It's common to use a "BUMP" post if you feel the need to bring it up the list. But you shouldn't use it within less than one hour...

It seems like you're expecting us to sit at our keyboards doing nothing else than waiting for your post to answer. You might want to change that expectation.

In case of an emergency, call 911, not SSC!

--

Thanks Moe. What is this BUMP u speak of.

LutzM SSC Guru Points: 107049 More actions · Answer 8

BaldingLoopMan (11/18/2010)

Thanks Moe. What is this BUMP u speak of.

A post with nothing but the word "bump" in it.

Usually used to bring the post back on the "most recent" list.

It's used on (at least) a daily base though, not per hour or even less...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 9

BaldingLoopMan

SSCertifiable

Points: 6378

November 18, 2010 at 2:57 pm

#1252767

noted

WayneS SSC Guru Points: 95405 More actions · Answer 10

BaldingLoopMan (11/18/2010)

For some reason simply putting the “for xml” makes my brain not want to learn it.

If you don't understand what's going on, just ask. This "FOR XML" "trick" is a really slick, high-speed way of performing string concatenation!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

LutzM (11/18/2010)

BaldingLoopMan (11/18/2010)

Thanks Moe. What is this BUMP u speak of.

A post with nothing but the word "bump" in it.

Usually used to bring the post back on the "most recent" list.

It's used on (at least) a daily base though, not per hour or even less...

The folks on this and other SQL Server forums get really ticked if you use the word "BUMP". Say something decent and, yes, do wait a couple of hours.

--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)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 12

A better query plan is produced if you make the following small modification:



SELECT DISTINCT

       attribute_id,

       Val = STUFF((SELECT ', ' + Val

                    FROM #tt

                    WHERE attribute_id = t1.attribute_id

                    ORDER BY attribute_id, Val

                    FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'')

  FROM #tt t1;

I'm not sure I share the view that using XML in this way is 'slick'. A proper language function, perhaps something like CONCAT() OVER (ORDER BY...) would be much nicer.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Paul White NZ (11/19/2010)

A better query plan is produced if you make the following small modification:

SELECT DISTINCT attribute_id, Val = STUFF((SELECT ', ' + Val FROM #tt WHERE attribute_id = t1.attribute_id ORDER BY attribute_id, Val FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(max)'),1,1,'') FROM #tt t1;

I'm not sure I share the view that using XML in this way is 'slick'. A proper language function, perhaps something like CONCAT() OVER (ORDER BY...) would be much nicer.

That an an honest to goodness, high performance split function would be damned nice. Getting SUM() OVER to work with PREVIOUS and NEXT rows would be bloody well handy, as well. 😉

--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)
Intro to Tally Tables and Functions