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 12»»

Transpose Rows to Columns with first column values as column names from the table?? Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 11:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:42 PM
Points: 16, Visits: 217
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.
Post #1389360
Posted Tuesday, November 27, 2012 12:01 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 945, Visits: 1,771
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

Jeremy Oursler
Post #1389362
Posted Tuesday, November 27, 2012 12:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:42 PM
Points: 16, Visits: 217
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.
Post #1389368
Posted Tuesday, November 27, 2012 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:44 PM
Points: 12,904, Visits: 31,978
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1389372
Posted Tuesday, November 27, 2012 12:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:42 PM
Points: 16, Visits: 217
Lowell, that link just points back to this forum post.
Post #1389375
Posted Tuesday, November 27, 2012 12:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:44 PM
Points: 12,904, Visits: 31,978
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1389385
Posted Tuesday, November 27, 2012 1:18 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:42 PM
Points: 16, Visits: 217
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!
Post #1389394
Posted Tuesday, November 27, 2012 2:51 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 945, Visits: 1,771
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

Jeremy Oursler
Post #1389423
Posted Tuesday, November 27, 2012 3:52 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 945, Visits: 1,771
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

Jeremy Oursler
Post #1389445
Posted Wednesday, November 28, 2012 8:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 15, 2014 12:42 PM
Points: 16, Visits: 217
Outstanding!

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!
Post #1389816
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse