November 10, 2008 at 1:06 pm
Hi all,
Here's the thing. We have a system where products are sold by size and the data is kept vertically. We use "Size Scales" that define the sizes.
Here's a short piece of code that will create a sample size scale.
Create Table SizeScaleHead
(
SizeScaleCode char(10),
SizeScaleDesc char(50)
)
Insert into SizeScaleHead select 'ScaleA', 'Whatever is scale A'
Create Table SizeScaleDetail
(
SizeScaleCode char(10),
SizeCodeSeq int,
SizeDesc char(5)
)
Insert into SizeScaleDetail
select 'ScaleA',1, '30'
union all
select 'ScaleA',2, '31'
union all
select 'ScaleA',3, '32'
union all
select 'ScaleA',4, '33'
union all
select 'ScaleA',5, '34'
union all
select 'ScaleA',6, '35'
union all
select 'ScaleA',7, '36'
select * from sizescalehead
select * from sizescaledetail
SizeScaleCode SizeScaleDesc
------------- --------------------------------------------------
ScaleA Whatever is scale A
SizeScaleCode SizeCodeSeq SizeDesc
------------- ----------- --------
ScaleA 1 30
ScaleA 2 31
ScaleA 3 32
ScaleA 4 33
ScaleA 5 34
ScaleA 6 35
ScaleA 7 36
These scales are used everywhere in our systems since everything is produced, sold and invoiced by size. Since the data is stored horizontally, there is no data for a size that isn't produced/invoiced for a particular order so 2 invoice detail records from different invoices could look like this.
InvoiceNoSizeQty
13010
13110
13310
23120
23525
I need to display this data horizontally on reports, text files or web sites. I'm trying to write a procedure to which I would pass a table variable which would return a single record with horizontal data. We have 30 sizes in our scales so the procedure should return 30 columns with the quantities for each size in it's corresponding column. Using my 7 sizes sample, if I ran the procedure for invoice # 1 I should end up with a table variable containing 7 columns and 1 record with the values 10,10,0,10,0,0,0.
That's the challenge, I don't know how to this in T-SQL.
November 10, 2008 at 1:31 pm
There's a couple of ways to handle this, but nothing that's particularly elegant. I would normally suggest handling this in the presentation layer. Nevertheless, here's a quick and dirty way of doing what you need:
Create Table sizeInvoice (invoiceNo int, int, qty int);
Insert Into sizeInvoice
Select 1, 30, 10 Union All
Select 1, 31, 10 Union All
Select 1, 33, 10
Select invoiceNo
, Sum(Case When = 30 Then qty Else 0 End) As 'size_30'
, Sum(Case When = 31 Then qty Else 0 End) As 'size_31'
, Sum(Case When = 32 Then qty Else 0 End) As 'size_32'
, Sum(Case When = 33 Then qty Else 0 End) As 'size_33'
From sizeInvoice
Group By invoiceNo;
You could also possibly solve this problem using pivot tables or prepared (dynamic) SQL statements.
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
November 10, 2008 at 1:54 pm
Michelle, thanks for your reply. However I can't do it this way, I need this to be linked to the SizeScale table because we're using different scales for different products. Certain products are made in sizes like 30,31,32 etc while others could be S,M,L,XL and so on.
I could have records in my invoice detail like:
InvoiceNo,Product, Size, Qty
1,A,30,5
1,A,31,5
2,B,S,10
2,B,L,15
When flipping the data to make it horizontal I need to do it in relation to the sizescales table, the sequence number of the sizescale table will become the column number of my horizontal data.
Sorry I didn't make this clearer in my first post.
November 10, 2008 at 2:33 pm
I've created a better script to provide test data.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SizeScaleDetail]') AND type in (N'U'))
DROP TABLE [dbo].[SizeScaleDetail]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SizeScaleHeader]') AND type in (N'U'))
DROP TABLE [dbo].[SizeScaleHeader]
Go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InvDetail]') AND type in (N'U'))
DROP TABLE [dbo].[InvDetail]
go
Create Table SizeScaleHeader
(
ScaleCode char(5),
ScaleDesc char(20)
)
Create Table SizeScaleDetail
(
ScaleCode char(50),
SizeSeq int,
SizeCode char(5)
)
Insert Into SizeScaleHeader
Select 'A', 'Scale A' union all
Select 'B', 'Scale B' union all
Select 'C', 'Scale C'
Insert into SizeScaleDetail
select 'A' ,1, '30' union all
select 'A' ,2, '32' union all
select 'A' ,3, '34' union all
select 'A' ,4, '36' union all
select 'A' ,5, '38' union all
select 'B', 1, 'S' union all
select 'B', 2, 'M' union all
select 'B', 3, 'L' union all
select 'B', 4, 'XL' union all
select 'C', 1, '5' union all
select 'C', 2, '6' union all
select 'C', 3, '7'
Create Table InvDetail
(
InvNo int,
ProductID Char(2),
Size char(5),
Quantity int
)
Insert Into InvDetail
Select 1, 'P1', '30', 1 union all
Select 1, 'P1', '32', 2 union all
Select 1, 'P1', '34', 3 union all
Select 1, 'P1', '36', 4 union all
Select 2, 'P1', '30', 10 union all
Select 2, 'P1', '32', 20 union all
Select 2, 'P1', '36', 30 union all
Select 3, 'P2', 'S', 100 union all
Select 3, 'P2', 'M', 200 union all
Select 3, 'P2', 'L', 300 union all
Select 4, 'P2', 'XL', 400 union all
Select 4, 'P2', 'M', 1000 union all
Select 4, 'P2', 'L', 2000 union all
Select 4, 'P2', 'XL', 3000
The presentation of the data needs to be like this
1, 'P1', 1, 2, 3, 4, 0
2, 'P1', 10, 20, 0, 30, 0
3, 'P2', 100, 200, 300, 0
4, 'P2', 0, 1000, 2000, 3000
So from the invoicedetail record I need to link to the sizescale table with the size from the invoice and the scale of the product, get the sequence number and put the quantity in the corresponding column. In my invoice #2, I have a 0 in column 3 and a 30 in column 4 because my 30 units are for size '36' which is the 4th size in the scale in which P1 is produced.
And I just found out that there's an extra difficulty......SQL 2005 doesn't allow passing table variables to a procedure. I was hoping I could create some universal code that would "horizontalize" anything but I'll look into it later. Right now I'll be happy if I can just find a way of doing it.
November 10, 2008 at 2:43 pm
A little advice try to do the pivoting on the client-side.
* Noel
November 10, 2008 at 3:03 pm
I'm playing with the idea of approaching it from the SizeScale table instead of the Invoice Table. For weeks now I've been trying to come up with a nice way of taking my vertical data and build a horizontal array from it but maybe I should build the record first, using the sizescale itself and run a loop to populate the buckets with the sizes I need.
That will require some dynamic SQL but it's certainly better than using a "curse her".
November 10, 2008 at 3:21 pm
How about a pivot table?
Select invNo, productID, [1] As col1, [2] As col2, [3] As col3, [4] As col4, 5 As [col5]
From (
Select invNo, productID, sizeSeq, Quantity
From dbo.SizeScaleDetail As ssd With (NoLock)
Join dbo.InvDetail As id With (NoLock)
On ssd.sizeCode = id.
) x
Pivot
(
Sum(Quantity)
For sizeSeq In ([1], [2], [3], [4], [5])
) As pvt
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
November 10, 2008 at 4:06 pm
select invNo,productID
,sum(case when sizeSeq = 1 then quantity else 0 end) as SizeSeq1
,sum(case when sizeSeq = 2 then quantity else 0 end) as SizeSeq2
,sum(case when sizeSeq = 3 then quantity else 0 end) as SizeSeq3
,sum(case when sizeSeq = 4 then quantity else 0 end) as SizeSeq4
,sum(case when sizeSeq = 5 then quantity else 0 end) as SizeSeq5
from #invDetail i
join #sizeScaleDetail s on s.sizeCode = i.size
group by invno,productID
------------------------------------------------------------------------------------------------
Note: the above gives 3400, not 3000, as the quantity for SizeSeq4 on invoice #4. Am I misunderstanding what you want, or did your example output have a typo?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 10, 2008 at 4:54 pm
eric (11/10/2008)
The presentation of the data needs to be like this
1, 'P1', 1, 2, 3, 4, 0
2, 'P1', 10, 20, 0, 30, 0
3, 'P2', 100, 200, 300, 0
4, 'P2', 0, 1000, 2000, 3000
Do you need the commas and the single quotes in the output?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 10, 2008 at 6:08 pm
No Jeff, I only put it there for ease of read.
Michelle's I tried your latest suggestion. I had looked into using a pivot before but couldn't make it work. Your code does better than mine which would not event pass the syntax checking but there's a problem in the results. The last column keeps the value of 5 (from the first record ?) for the orders that have have nothing in the 5th size.
bhovious, your code seems to work well, I will have to try it later with real data and all the other columns that are involved as the ProductID in the invoice detail is actually 5 different fields (ProductCode, Color etc) but I don't see why it wouldn't work.
I'll get back to you later and let you know how well it does.
November 11, 2008 at 2:50 am
Hi eric,
u can try this.................
select invno,ProductID,
(Select sum(case when size='30' then Quantity else 0 end )) as '30',
(Select sum(case when size='32' then Quantity else 0 end )) as '32',
(select sum(case when size='34' then Quantity else 0 end )) as '34',
(Select sum(case when size='36' then Quantity else 0 end )) as '36',
(Select sum(case when size='S'then Quantity else 0 end )) as 'S',
(Select sum(case when size='M'then Quantity else 0 end )) as 'M',
(Select sum(case when size='L'then Quantity else 0 end )) as 'L',
(Select sum(case when size='XL' then Quantity else 0 end )) as 'XL'
From invdetail
group by invno,ProductID
Shailesh
November 11, 2008 at 4:32 am
Hi Shailesh,
That's similar to bhovious' solution but yours requires that I know the sizes which are in a table. If the sizescales are modified it would no longer work.
November 11, 2008 at 7:44 am
Hi Eric,
Sorry, I had a typo. Basically, the last column showed "5" instead of "[5]", so a static value of 5 was being filled down. It's supposed to be NULL to illustrate what would happen if no inventory is available in that size. Here's the updated code:
Select invNo, productID, [1] As col1, [2] As col2, [3] As col3, [4] As col4, [5] As [col5]
From (
Select invNo, productID, sizeSeq, Quantity
From dbo.SizeScaleDetail As ssd With (NoLock)
Join dbo.InvDetail As id With (NoLock)
On ssd.sizeCode = id.
) x
Pivot
(
Sum(Quantity)
For sizeSeq In ([1], [2], [3], [4], [5])
) As pvt
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
November 11, 2008 at 10:06 am
Thanks Michelle,
I should have paid more attention, I didn't see it.
November 11, 2008 at 8:03 pm
Eric, it sounds as if you want to have variable number of columns in the output.
If that is the case you would need to make a dynamic pivot.
Let me know if that's what you need.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply