SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transpose Rows to Columns with first column values as column names from the table??


Transpose Rows to Columns with first column values as column names from the table??

Author
Message
lynn.huff
lynn.huff
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 271
I've been banging my head on this for a couple of days now, and I feel the answer is just out of my grasp. I need to replace this query:

Declare @ChainCode int
Set @ChainCode = 970

Select EffectiveDate, CanDrinks, Bottle20CSD, Snacks, Coffee, Food,
Juice, Gatorade, IceCream, Sundry
From CommRates
Where ChainCode = @ChainCode



Which produces this result set,

EffectiveDate CanDrinks Bottle20CSD Snacks Coffee Food Juice Gatorade IceCream Sundry
2006-12-18 0.1900 0.1900 0.1100 0.1000 0.1000 0.1000 0.1000 NULL NULL
2008-04-01 0.1700 0.1700 0.1100 0.1500 0.1500 0.1500 0.1700 0.1500 NULL

with a query that will produce this...

Category 2006-12-18 2008-04-01
CanDrinks 0.1900 0.1700
BottleCSD 0.1900 0.1700
Snacks 0.1100 0.1100
Coffee 0.1000 0.1500
Food 0.1000 0.1500
Juice 0.1000 0.1500
Gatorade 0.1000 0.1700
IceCream Null 0.1500
Sundry Null Null

Can anyone point me in the right direction? Please note that the number of columns in the transposed table will be dynamic based on how many records are found that match the where clause in the first query. The name of the first column in my desired result could be anything. I inserted the word "category" here for illustration. Also, I do not want to perform any aggregation on any of the data. I have to write a number of reports that need this same type of logic where column names are arranged as a single column in the first column of a new table, and the number of columns in the final table being dynamic based on dates.
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1789
I would recommend reading Jeff Moden's Cross tab and Pivot articles. Number 1 is http://www.sqlservercentral.com/articles/T-SQL/63681/


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
lynn.huff
lynn.huff
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 271
I read the article completely, but I can't see how it applies to the specifics of my problem. I need a dynamic number of columns, with the first columns rows being made up of the column names of the original table.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28472 Visits: 39967
part two of the group of articles that cpn hector mentioned is doing it dynamically, when the # of values is unknown.

the link to part two is here:
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lynn.huff
lynn.huff
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 271
Lowell, that link just points back to this forum post.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28472 Visits: 39967
lynn.huff (11/27/2012)
Lowell, that link just points back to this forum post.


whoops! link fixed, i built the tag wrong, sorry.

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lynn.huff
lynn.huff
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 271
That all applies to aggregating, which doesn't apply to my problem.

Here's some code that ALMOST does what I want. It will supply the dynamic columns, BUT only give me the values for ONE of my rows. But, it is missing the first column I need in the result set, which should contain the column names from the original table.

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast(EffectiveDate as varchar) + ']',
'[' + cast(EffectiveDate as varchar)+ ']'
)
FROM TCAP.dbo.CommRates
Where ChainCode = 970
Print @PivotColumnHeaders


DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT *
FROM (
SELECT
P.EffectiveDate,
P.CanDrinks
FROM CommRates P
Where ChainCode = 970
) AS PivotData
PIVOT (
SUM(CanDrinks)
FOR EffectiveDate IN (
' + @PivotColumnHeaders + '
)

) AS PivotTable
'

EXECUTE(@PivotTableSQL)



Can anyone suggest how to finish this to get my desired result? Thanks!
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1789
I will try to work something up but with out sample data it will take a little longer as i will need to generate some. As far as aggregating you can always aggregate a set of 1 (MAX(1) is 1)


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
CapnHector
CapnHector
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1789
Looking at the original query it is very de-normalized and almost looks like a pivot table in its own right so the first thing i did to accomplish the task was to UNPIVOT the data (i used a cross apply) First i generated some sample data

CREATE TABLE SampleData (
EffectiveDate DATE,
CanDrinks INT, --ints are easy to generate
Bottle20CSD INT,
Snacks INT,
Coffee INT,
Food INT
--..... this gives enough columns to get the point accross
)


INSERT INTO SampleData
SELECT DATEADD(DD,(ABS(CHECKSUM(NEWID())) % 25) * -1,GETDATE()),
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000
FROM Tally
WHERE N < 20

;WITH Dupes AS (SELECT ROW_NUMBER() OVER (PARTITION BY EffectiveDate ORDER BY CanDrinks) AS RN, *
FROM SampleData)

DELETE FROM Dupes WHERE RN > 1


SELECT * FROM SampleData



Then with data that is close to what i think yours is i normalized the data on the fly with

SELECT EffectiveDate, ItemName, Ammount
FROM SampleData
CROSS APPLY (VALUES ('CanDrinks',CanDrinks),('Bottle20CSD',Bottle20CSD),('Snacks',Snacks),('Coffee',Coffee),('Food',Food))x(ItemName,Ammount)



This will allow us to make a nice pivot table in the output you want. With the unpivot you can then write your dynamic pivot table (or cross tab) in the same manor you were attacking the problem before. if you have any other questions let me know.


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
lynn.huff
lynn.huff
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 271
Outstanding! :-D:-D

I had not used Cross Apply before. Your idea of using Cross Apply, along with dynamically creating the column list for the pivot table solved my problem. :-)

Thank you very much!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search