Need to convert 1 column into multiple columns

J

Java

Hi, I have a table that has a column called ShipDate. I need to have
ShipDate in multiple columns - Date1, Date2, Date3, etc... (not the actual
date as the column).
The cross tab gives me the actual date as column which is not what is
needed.

This is just a sample. I'm not sure why this is needed but it is being
requested of me.

MyTable
CustomerID ProductID Quantity ShipDate
123 T100 5 8/23/2008
123 T100 5 8/24/2008
123 T100 5 8/25/2008
123 T200 10 8/23/2008
123 T200 10 8/24/2008
123 T200 10 8/25/2008
123 T300 15 8/23/2008
123 T300 15 8/24/2008
123 T300 15 8/25/2008
456 T500 5 8/25/2008
456 T500 5 8/26/2008
456 T500 5 8/27/2008
456 T600 10 8/25/2008
456 T600 10 8/26/2008
456 T600 10 8/27/2008
456 T700 15 8/25/2008
456 T700 15 8/26/2008
456 T700 15 8/27/2008


Crosstab
CustomerID ProductID Quantity 8/23/2008 8/24/2008 8/25/2008 8/26/2008 8/27/2008
123 T100 5 8/23/2008 8/24/2008 8/25/2008
123 T200 10 8/23/2008 8/24/2008 8/25/2008
123 T300 15 8/23/2008 8/24/2008 8/25/2008
456 T500 5 8/25/2008 8/26/2008 8/27/2008
456 T600 10 8/25/2008 8/26/2008 8/27/2008
456 T700 15 8/25/2008 8/26/2008 8/27/2008

Need for data to look like this
CustomerID ProductID Quantity Date1 Date2 Date3 Date4 … Date5n
123 T100 5 8/23/2008 8/24/2008 8/25/2008
123 T200 10 8/23/2008 8/24/2008 8/25/2008
123 T300 15 8/23/2008 8/24/2008 8/25/2008
456 T500 5 8/25/2008 8/26/2008 8/27/2008
456 T600 10 8/25/2008 8/26/2008 8/27/2008
456 T700 15 8/25/2008 8/26/2008 8/27/2008


Thanks!
 
K

KARL DEWEY

One way is to use a Ranking in a Group query to produce a temp table then
crosstab query off that.

SELECT Q.CustomerID, Q.ProductID, Q.Quanity, Q.ShipDate, (SELECT COUNT(*)
FROM [MyTable] Q1
WHERE Q1.[CustomerID] = Q.[CustomerID]
AND Q1.[ProductID] = Q.[ProductID]
AND Q1.[ShipDate] < Q.[ShipDate])+1 AS Rank INTO MyTable_ShipOrder
FROM MyTable AS Q
ORDER BY Q.CustomerID, Q.ProductID, Q.Quanity, Q.ShipDate;

TRANSFORM First(MyTable_ShipOrder.ShipDate) AS FirstOfShipDate
SELECT MyTable_ShipOrder.CustomerID, MyTable_ShipOrder.ProductID,
MyTable_ShipOrder.Quanity
FROM MyTable_ShipOrder
GROUP BY MyTable_ShipOrder.CustomerID, MyTable_ShipOrder.ProductID,
MyTable_ShipOrder.Quanity
PIVOT "Date" & [Rank];
 
V

vbasean

Here's my thinking but I haven't fully figured it out.

If you could add a column on to your original table of "DateCounter" and had
it iterate per CustomerID

MyTable
CustomerID ProductID Quantity
ShipDate DateCount
123 T100 5 8/23/2008 Date1
123 T100 5 8/24/2008 Date2
123 T100 5 8/25/2008 Date3
123 T200 10 8/23/2008 Date1
123 T200 10 8/24/2008 Date2
123 T200 10 8/25/2008 Date…
123 T300 15 8/23/2008 Date…

then you could pivot off that DateCount Column
 
V

vbasean

Karl,

You know way too much about this stuff! Great answer. I had the concept
but not the know how. Another trinket to add to my knowledge base.

I was twiddling this around in my head and hoping someone would come along
with the answer.

Thanks!

KARL DEWEY said:
One way is to use a Ranking in a Group query to produce a temp table then
crosstab query off that.

SELECT Q.CustomerID, Q.ProductID, Q.Quanity, Q.ShipDate, (SELECT COUNT(*)
FROM [MyTable] Q1
WHERE Q1.[CustomerID] = Q.[CustomerID]
AND Q1.[ProductID] = Q.[ProductID]
AND Q1.[ShipDate] < Q.[ShipDate])+1 AS Rank INTO MyTable_ShipOrder
FROM MyTable AS Q
ORDER BY Q.CustomerID, Q.ProductID, Q.Quanity, Q.ShipDate;

TRANSFORM First(MyTable_ShipOrder.ShipDate) AS FirstOfShipDate
SELECT MyTable_ShipOrder.CustomerID, MyTable_ShipOrder.ProductID,
MyTable_ShipOrder.Quanity
FROM MyTable_ShipOrder
GROUP BY MyTable_ShipOrder.CustomerID, MyTable_ShipOrder.ProductID,
MyTable_ShipOrder.Quanity
PIVOT "Date" & [Rank];

--
KARL DEWEY
Build a little - Test a little


Java said:
Hi, I have a table that has a column called ShipDate. I need to have
ShipDate in multiple columns - Date1, Date2, Date3, etc... (not the actual
date as the column).
The cross tab gives me the actual date as column which is not what is
needed.

This is just a sample. I'm not sure why this is needed but it is being
requested of me.

MyTable
CustomerID ProductID Quantity ShipDate
123 T100 5 8/23/2008
123 T100 5 8/24/2008
123 T100 5 8/25/2008
123 T200 10 8/23/2008
123 T200 10 8/24/2008
123 T200 10 8/25/2008
123 T300 15 8/23/2008
123 T300 15 8/24/2008
123 T300 15 8/25/2008
456 T500 5 8/25/2008
456 T500 5 8/26/2008
456 T500 5 8/27/2008
456 T600 10 8/25/2008
456 T600 10 8/26/2008
456 T600 10 8/27/2008
456 T700 15 8/25/2008
456 T700 15 8/26/2008
456 T700 15 8/27/2008


Crosstab
CustomerID ProductID Quantity 8/23/2008 8/24/2008 8/25/2008 8/26/2008 8/27/2008
123 T100 5 8/23/2008 8/24/2008 8/25/2008
123 T200 10 8/23/2008 8/24/2008 8/25/2008
123 T300 15 8/23/2008 8/24/2008 8/25/2008
456 T500 5 8/25/2008 8/26/2008 8/27/2008
456 T600 10 8/25/2008 8/26/2008 8/27/2008
456 T700 15 8/25/2008 8/26/2008 8/27/2008

Need for data to look like this
CustomerID ProductID Quantity Date1 Date2 Date3 Date4 … Date5n
123 T100 5 8/23/2008 8/24/2008 8/25/2008
123 T200 10 8/23/2008 8/24/2008 8/25/2008
123 T300 15 8/23/2008 8/24/2008 8/25/2008
456 T500 5 8/25/2008 8/26/2008 8/27/2008
456 T600 10 8/25/2008 8/26/2008 8/27/2008
456 T700 15 8/25/2008 8/26/2008 8/27/2008


Thanks!
Click to expand...
Click to expand...
 
J

Java

HI,
Thank you both. I will try this.


KARL DEWEY said:
One way is to use a Ranking in a Group query to produce a temp table then
crosstab query off that.

SELECT Q.CustomerID, Q.ProductID, Q.Quanity, Q.ShipDate, (SELECT COUNT(*)
FROM [MyTable] Q1
WHERE Q1.[CustomerID] = Q.[CustomerID]
AND Q1.[ProductID] = Q.[ProductID]
AND Q1.[ShipDate] < Q.[ShipDate])+1 AS Rank INTO MyTable_ShipOrder
FROM MyTable AS Q
ORDER BY Q.CustomerID, Q.ProductID, Q.Quanity, Q.ShipDate;

TRANSFORM First(MyTable_ShipOrder.ShipDate) AS FirstOfShipDate
SELECT MyTable_ShipOrder.CustomerID, MyTable_ShipOrder.ProductID,
MyTable_ShipOrder.Quanity
FROM MyTable_ShipOrder
GROUP BY MyTable_ShipOrder.CustomerID, MyTable_ShipOrder.ProductID,
MyTable_ShipOrder.Quanity
PIVOT "Date" & [Rank];

--
KARL DEWEY
Build a little - Test a little


Java said:
Hi, I have a table that has a column called ShipDate. I need to have
ShipDate in multiple columns - Date1, Date2, Date3, etc... (not the actual
date as the column).
The cross tab gives me the actual date as column which is not what is
needed.

This is just a sample. I'm not sure why this is needed but it is being
requested of me.

MyTable
CustomerID ProductID Quantity ShipDate
123 T100 5 8/23/2008
123 T100 5 8/24/2008
123 T100 5 8/25/2008
123 T200 10 8/23/2008
123 T200 10 8/24/2008
123 T200 10 8/25/2008
123 T300 15 8/23/2008
123 T300 15 8/24/2008
123 T300 15 8/25/2008
456 T500 5 8/25/2008
456 T500 5 8/26/2008
456 T500 5 8/27/2008
456 T600 10 8/25/2008
456 T600 10 8/26/2008
456 T600 10 8/27/2008
456 T700 15 8/25/2008
456 T700 15 8/26/2008
456 T700 15 8/27/2008


Crosstab
CustomerID ProductID Quantity 8/23/2008 8/24/2008 8/25/2008 8/26/2008 8/27/2008
123 T100 5 8/23/2008 8/24/2008 8/25/2008
123 T200 10 8/23/2008 8/24/2008 8/25/2008
123 T300 15 8/23/2008 8/24/2008 8/25/2008
456 T500 5 8/25/2008 8/26/2008 8/27/2008
456 T600 10 8/25/2008 8/26/2008 8/27/2008
456 T700 15 8/25/2008 8/26/2008 8/27/2008

Need for data to look like this
CustomerID ProductID Quantity Date1 Date2 Date3 Date4 … Date5n
123 T100 5 8/23/2008 8/24/2008 8/25/2008
123 T200 10 8/23/2008 8/24/2008 8/25/2008
123 T300 15 8/23/2008 8/24/2008 8/25/2008
456 T500 5 8/25/2008 8/26/2008 8/27/2008
456 T600 10 8/25/2008 8/26/2008 8/27/2008
456 T700 15 8/25/2008 8/26/2008 8/27/2008


Thanks!
Click to expand...
Click to expand...
 
J

Java

I tried this & it is exactly what I am looking for. Thank you sooooooooo
much!!!!!!



KARL DEWEY said:
One way is to use a Ranking in a Group query to produce a temp table then
crosstab query off that.

SELECT Q.CustomerID, Q.ProductID, Q.Quanity, Q.ShipDate, (SELECT COUNT(*)
FROM [MyTable] Q1
WHERE Q1.[CustomerID] = Q.[CustomerID]
AND Q1.[ProductID] = Q.[ProductID]
AND Q1.[ShipDate] < Q.[ShipDate])+1 AS Rank INTO MyTable_ShipOrder
FROM MyTable AS Q
ORDER BY Q.CustomerID, Q.ProductID, Q.Quanity, Q.ShipDate;

TRANSFORM First(MyTable_ShipOrder.ShipDate) AS FirstOfShipDate
SELECT MyTable_ShipOrder.CustomerID, MyTable_ShipOrder.ProductID,
MyTable_ShipOrder.Quanity
FROM MyTable_ShipOrder
GROUP BY MyTable_ShipOrder.CustomerID, MyTable_ShipOrder.ProductID,
MyTable_ShipOrder.Quanity
PIVOT "Date" & [Rank];

--
KARL DEWEY
Build a little - Test a little


Java said:
Hi, I have a table that has a column called ShipDate. I need to have
ShipDate in multiple columns - Date1, Date2, Date3, etc... (not the actual
date as the column).
The cross tab gives me the actual date as column which is not what is
needed.

This is just a sample. I'm not sure why this is needed but it is being
requested of me.

MyTable
CustomerID ProductID Quantity ShipDate
123 T100 5 8/23/2008
123 T100 5 8/24/2008
123 T100 5 8/25/2008
123 T200 10 8/23/2008
123 T200 10 8/24/2008
123 T200 10 8/25/2008
123 T300 15 8/23/2008
123 T300 15 8/24/2008
123 T300 15 8/25/2008
456 T500 5 8/25/2008
456 T500 5 8/26/2008
456 T500 5 8/27/2008
456 T600 10 8/25/2008
456 T600 10 8/26/2008
456 T600 10 8/27/2008
456 T700 15 8/25/2008
456 T700 15 8/26/2008
456 T700 15 8/27/2008


Crosstab
CustomerID ProductID Quantity 8/23/2008 8/24/2008 8/25/2008 8/26/2008 8/27/2008
123 T100 5 8/23/2008 8/24/2008 8/25/2008
123 T200 10 8/23/2008 8/24/2008 8/25/2008
123 T300 15 8/23/2008 8/24/2008 8/25/2008
456 T500 5 8/25/2008 8/26/2008 8/27/2008
456 T600 10 8/25/2008 8/26/2008 8/27/2008
456 T700 15 8/25/2008 8/26/2008 8/27/2008

Need for data to look like this
CustomerID ProductID Quantity Date1 Date2 Date3 Date4 … Date5n
123 T100 5 8/23/2008 8/24/2008 8/25/2008
123 T200 10 8/23/2008 8/24/2008 8/25/2008
123 T300 15 8/23/2008 8/24/2008 8/25/2008
456 T500 5 8/25/2008 8/26/2008 8/27/2008
456 T600 10 8/25/2008 8/26/2008 8/27/2008
456 T700 15 8/25/2008 8/26/2008 8/27/2008


Thanks!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

WCG Stats Thursday 10 August 2023 3
WCG Stats Monday 09 October 2023 4
WCG Stats Wednesday 04 October 2023 3
WCG Stats Friday 28 July 2023 3
WCG Stats Tuesday 11 July 2023 3
WCG Stats Friday 20 January 2023 2
WCG Stats Wednesday 21 June 2023 3
WCG Stats Friday 06 October 2023 3

Top