August 11, 2016 at 2:23 am
Hi all,
i have a crosstab table called Category for example:
ProdID 01/08 02/08 03/08 04/08
1 A S R W
2 Q W R T
3 D F G H
and so on.
The output i require is the following:
Date OrderID prodID QTY category
01/08 001 1 3 A
01/08 001 3 1 D
02/08 002 2 1 W
02/08 002 3 1 F
The order data is in another table 'Orders' and contains fields [Date], [OrderID], [prodID] and [QTY]
if a customer (001) has ordered prodID 1 and 3 on 01/08, then category will be A and D.
if a customer (002) has ordered prodID 2 and 3 on 02/08, then category will be W and F.
the trouble im having is using the date in orders table which is raw data, and trying to link with table headings in category table which goes across the top.
Thank you in advanced.
August 11, 2016 at 9:56 am
Your sample data has no OrderID or Qty, so it's not clear how to get those values. I suspect that you want an UNPIVOT, although I prefer the alternate method that uses a CROSS APPLY.
/*Set up sample data*/
CREATE TABLE #Sample (
ProdID INT,
[01/08] CHAR(1),
[02/08] CHAR(1),
[03/08] CHAR(1),
[04/08] CHAR(1)
)
INSERT #Sample VALUES
(1, 'A', 'S', 'R', 'W'),
(2, 'Q', 'W', 'R', 'T'),
(3, 'D', 'F', 'G', 'H')
/*Actual query*/
SELECT s.ProdID, v.dt, v.category
FROM #Sample s
CROSS APPLY (
VALUES
('01/08', .[01/08]),
('02/08', .[02/08]),
('03/08', .[03/08]),
('04/08', .[04/08])
) v(dt, category)
If that's not what you are looking for, can you pleas post more complete data?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 11, 2016 at 10:18 am
Close..but not quiet.
sample data as follows:
CREATE TABLE #Crosstab (
ProdID INT,
[01/08] CHAR(1),
[02/08] CHAR(1),
[03/08] CHAR(1),
[04/08] CHAR(1)
)
INSERT #Crosstab VALUES
(1, 'A', 'S', 'R', 'W'),
(2, 'Q', 'W', 'R', 'T'),
(3, 'D', 'F', 'G', 'H')
CREATE TABLE #Orders (
Date CHAR(5),
OrderID INT,
ProdID INT,
QTY INT
)
INSERT #Orders VALUES
('01/08', 1, 1, 3),
('01/08', 1, 3, 1),
('02/08', 2, 2, 1),
('02/08', 2, 3, 1)
I would like the result to be:
DateOrderIDProdIDQTYCAT
01/08113A
01/08131D
02/08221W
02/08231F
so as order 1 was ordered on 01/08, it looks in the column headed '01/08' in the #Crosstab table, and matches the prodID to gain the category.
order 2 was ordered on 02/08, it looks in the column headed '02/08' in the #Crosstab table, and matches the prodID to gain the category.
this is similar to a =INDEX(... , MATCH(.. , .. , ..), MATCH(.. , .. , ..)) in excel.
thanks
August 11, 2016 at 12:07 pm
It's not a dynamic solution but something like this gets the output you want.
SELECT o.[Date], o.OrderID, x.ProdID, o.QTY, x.Category
FROM #Crosstab c
UNPIVOT (
Category FOR [Date] IN ([01/08], [02/08], [03/08], [04/08])
) x
JOIN #Orders o ON o.ProdID = x.ProdID AND o.[Date] = x.[Date]
EDIT:
Here is a dynamic example...
DECLARE @myDates VARCHAR(MAX), @stmt VARCHAR(Max)
SET @myDates = (SELECT DISTINCT '['+[Date]+'],' FROM #Orders FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)')
SET @myDates = SUBSTRING(@myDates, 1, LEN(@myDates)-1)
SET @stmt = 'SELECT o.[Date], o.OrderID, x.ProdID, o.QTY, x.Category
FROM #Crosstab c
UNPIVOT (
Category FOR [Date] IN ('+ @myDates + ')
) x
JOIN #Orders o ON o.ProdID = x.ProdID AND o.[Date] = x.[Date]'
EXECUTE(@stmt)
August 11, 2016 at 12:07 pm
Talvin Singh (8/11/2016)
Close..but not quiet.
It was as close as I could get with the original data provided. Incorporating the additional data is fairly straightforward and is left as an exercise for the original poster.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 15, 2016 at 3:01 am
Hi,
that works very well. exactly what i was looking for.
if dates are in the numeric format, then i get the following error:
CREATE TABLE #Crosstab (
ProdID INT,
[42128] CHAR(1),
[42129] CHAR(1),
[42130] CHAR(1),
[42131] CHAR(1)
)
INSERT #Crosstab VALUES
(1, 'A', 'S', 'R', 'W'),
(2, 'Q', 'W', 'R', 'T'),
(3, 'D', 'F', 'G', 'H')
CREATE TABLE #Orders (
Date INT,
OrderID INT,
ProdID INT,
QTY INT
)
INSERT #Orders VALUES
(42128, 1, 1, 3),
(42128, 1, 3, 1),
(42129, 2, 2, 1),
(42129, 2, 3, 1)
DECLARE @stmt VARCHAR(Max), @myDates VARCHAR(MAX)
SET @myDates = (SELECT DISTINCT [Date] FROM #Orders FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)')
SET @myDates = SUBSTRING(@myDates, 1, LEN(@myDates)-1)
SET @stmt = 'SELECT o.[Date], o.OrderID, x.ProdID, o.QTY, x.Category
FROM #Crosstab c
UNPIVOT (
Category FOR [Date] IN ('+ @myDates + ')
) x
JOIN #Orders o ON o.ProdID = x.ProdID AND o.[Date] = x.[Date]'
EXECUTE(@stmt)
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near '421284212'.
August 15, 2016 at 7:01 am
Your errors come from the fact that you changed your CHAR column to integer. The concatenation needs to occur on string values when you're building your list, so try this:
SET @myDates = (SELECT DISTINCT '[' + CONVERT(Varchar(5), [Date]) + '],'
FROM #Orders
FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)')
You'll still need to remove your trailing comma, or you could perform both in a single step by switching to a leading delimiter and then using STUFF to replace the first one like this:
SELECT @myDates = STUFF((SELECT DISTINCT ',[' + CONVERT(Varchar(5), [Date]) + ']'
FROM #Orders
FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)'), 1, 1, '');
August 15, 2016 at 7:07 am
You forgot the commas and to put brackets around your column names.
August 15, 2016 at 7:31 am
Y.B. (8/11/2016)
It's not a dynamic solution but something like this gets the output you want.
SELECT o.[Date], o.OrderID, x.ProdID, o.QTY, x.CategoryFROM #Crosstab c
UNPIVOT (
Category FOR [Date] IN ([01/08], [02/08], [03/08], [04/08])
) x
JOIN #Orders o ON o.ProdID = x.ProdID AND o.[Date] = x.[Date]
EDIT:
Here is a dynamic example...
DECLARE @myDates VARCHAR(MAX), @stmt VARCHAR(Max)SET @myDates = (SELECT DISTINCT '['+[Date]+'],' FROM #Orders FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)')
SET @myDates = SUBSTRING(@myDates, 1, LEN(@myDates)-1)
SET @stmt = 'SELECT o.[Date], o.OrderID, x.ProdID, o.QTY, x.Category
FROM #Crosstab c
UNPIVOT (
Category FOR [Date] IN ('+ @myDates + ')
) x
JOIN #Orders o ON o.ProdID = x.ProdID AND o.[Date] = x.[Date]'
EXECUTE(@stmt)
That's simple enough, all you need to do is convert the integer into a character string. But even with that it will fail because you omitted the brackets from my code above. Those are in there because they are part of columns names you defined. i.e. 42128 does not equal [42128]
Run this for fun:
DECLARE @columnTest TABLE ([Date] DATE, "[Date]" DATE)
SELECT * FROM @columnTest
So what you want is to change it back to this:
SET @myDates = (SELECT DISTINCT '[' + CONVERT(VARCHAR(5),[Date]) + '],' FROM #Orders FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)')
Again I know this is test data but I should at least warn you to not use column names like that, also try to avoid naming columns with reserved keywords. Instead maybe call it 'OrderDate' for example. I hope that your crosstab table is just a temporary working table because you really don't want that kind of design either.
August 15, 2016 at 9:15 am
ah ok, makes sense.
that definitely worked.
i tried to convert to varchar but it was the square brackets that was missing.
very helpful all!
😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply