Question on Pitvoting data in SQL

Question

Post reply

Question on Pitvoting data in SQL

Chris Hurlbut

SSCarpal Tunnel

Points: 4162
More actions
April 5, 2012 at 8:05 am

#391956

Hello All,
Hope there is a simple answer to this problem.
I need to take the following data:
Table1
StoreNumberProduct
A Carrot
AOnion
APea
Bpotato
Blettuce
CBeer
CWine
CPop
CIce Tea
And produce this:
StoreNumberProducts
ACarrotOnionPea
Bpotatolettuce
CBeerWinePopIce Tea
The key is there are over 860 Stores and the stores will change each run, I attempted to use the PIVOT command but I am not aggregating my data in any way and didn’t find any good examples.
I am stumped on this and just need the t-sql to take what is in table1 and produce a row with the store number and all the associated products.

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

looks like a case for a cross-tab, see the links in my signature on how to get started

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

With 600+ points surely you know you should provide ddl and sample data. Please do at least that much next time.

Try this.

declare @tbl table (

StoreNumber char(1),

Product varchar(25)

);

insert @tbl(StoreNumber, Product)

select 'A', 'Carrot' union all

select 'A', 'Onion' union all

select 'A', 'Pea' union all

select 'B', 'potato' union all

select 'B', 'lettuce' union all

select 'C', 'Beer' union all

select 'C', 'Wine' union all

select 'C', 'Pop' union all

select 'C', 'Ice Tea'

select StoreNumber, stuff((

select ' ' + t.Product

from @tbl t

where t.StoreNumber = m.StoreNumber

for xml path(''), type

).value('.','varchar(max)'), 1, 1, '') as Products

from @tbl m

group by StoreNumber

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Mark Fitzgerald-331224 SSCrazy Eights Points: 8489 More actions · Answer 3

Could try a clr aggregate for concatenate...

http://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/">

http://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/

This would return the products in a single column and allow a comma separated list

Fitz

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Unless the product list is going to exceed 8,000 characters there is no need to use CLR for this. The code I provided is a delimited list with no CLR or looping.

This will more clearly demonstrate where the delimiter is:

declare @Delimiter char(1) = ','

select StoreNumber, stuff((

select @Delimiter + t.Product

from @tbl t

where t.StoreNumber = m.StoreNumber

for xml path(''), type

).value('.','varchar(max)'), 1, 1, '') as Products

from @tbl m

group by StoreNumber

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 5

Chris Hurlbut

SSCarpal Tunnel

Points: 4162

April 5, 2012 at 8:52 am

#1469590

Thanks Sean your solution worked like a charm.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

You're welcome. Thanks for letting us know that worked for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dwain Camps SSC Guru Points: 86968 More actions · Answer 7

Ooops.. Bad idea... Never mind.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 8

Sean Lange (4/5/2012)
Unless the product list is going to exceed 8,000 characters there is no need to use CLR for this. The code I provided is a delimited list with no CLR or looping.
This will more clearly demonstrate where the delimiter is:
declare @Delimiter char(1) = ','
select StoreNumber, stuff((
select @Delimiter + t.Product
from @tbl t
where t.StoreNumber = m.StoreNumber
for xml path(''), type
).value('.','varchar(max)'), 1, 1, '') as Products
from @tbl m
group by StoreNumber

The code you provided works well even if you exceed 8000 characters.

--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)

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Jeff Moden (4/9/2012)
Sean Lange (4/5/2012)
Unless the product list is going to exceed 8,000 characters there is no need to use CLR for this. The code I provided is a delimited list with no CLR or looping.
This will more clearly demonstrate where the delimiter is:
declare @Delimiter char(1) = ','
select StoreNumber, stuff((
select @Delimiter + t.Product
from @tbl t
where t.StoreNumber = m.StoreNumber
for xml path(''), type
).value('.','varchar(max)'), 1, 1, '') as Products
from @tbl m
group by StoreNumber
The code you provided works well even if you exceed 8000 characters.

Thanks Jeff. Wasn't sure how this would scale on varchar(max). I had always heard that was somewhat of a point where you should at least think about considering CLR instead of for xml. I don't routinely work with any strings that long so I don't have much practice with them. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 10

Joining a Tally Table to VARCHAR(MAX) (during delimited splitting, for example), even if there are less than 8000 characters, will cause the join to run twice as slow as an equivalent VARCHAR(8000) example. Other than that, most things will stay the same.

On the CLR thing, "It Depends". 😉

--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)