0

I have 2 tables in sql-server 2005 express DB

1. ItemSize Table

CREATE TABLE [dbo].[ItemSize](
[ID] [int] IDENTITY(1,1) NOT NULL,
[iSize] [int] NULL
) ON [PRIMARY]

2. SalesLog Table

CREATE TABLE [dbo].[SalesLog](
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[MemoNo] [int] NULL,
[ProductCode] [int] NULL,
[Quantity] [int] NULL,
[Price] [int] NULL,
[pGroup] [int] NULL,
[pName] [nvarchar](30) NULL,
[pSize] [int] NULL,
[BillDate] [nchar](10) NULL,
CONSTRAINT [PK_SalesLog] PRIMARY KEY CLUSTERED 
(
[SalesID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ItemSize table contains different size of drink like

100ml
200ml
300ml
400ml
500ml

I want ItemSize table columns (iSize) data as column like

ItemName   100ml    200ml    300ml   400ml   500ml
 Drink A    10       20        5       4      30
 Drink B    20       10        15      35     40

and data will be arranged from Sales table according to size and Product Name as shown above.

I have similar query to do so but its not dynamic. i mean if i add new size to ItemSize table then it will not be avilable to report. So i need solution for this.

Currently I am using this query to fetch sales according to item size

select i.gName,
isnull((select 
sum(Quantity)from saleslog where BillDate='01-06-2010' and pSize=0 and 
pGroup=i.gCode),0) as '0ml', 
isnull((select 
sum(Quantity)from saleslog where BillDate='01-06-2010' and pSize=180 and 
pGroup=i.gCode),0) as '180ml', 
isnull((select 
sum(Quantity)from saleslog where BillDate='01-06-2010' and pSize=375 and 
pGroup=i.gCode),0) as '375ml', 
isnull((select 
sum(Quantity)from saleslog where BillDate='01-06-2010' and pSize=500 and 
pGroup=i.gCode),0) as '500ml',
isnull((select 
sum(Quantity)from saleslog where BillDate='01-06-2010' and pSize=650 and 
pGroup=i.gCode),0) as '650ml',
isnull((select 
sum(Quantity)from saleslog where BillDate='01-06-2010' and pSize=750 and 
pGroup=i.gCode),0) as '750ml',
isnull((select 
sum(Quantity)from saleslog where BillDate='01-06-2010' and pSize=1000 and 
pGroup=i.gCode),0) as '1000ml',
isnull((select 
sum(Quantity)from saleslog where BillDate='01-06-2010' and 
pSize=2000 and pGroup=i.gCode),0) as '2000ml' 
from saleslog as s 
inner join ItemGroup as i on s.pGroup=i.gCode 
where BillDate='01-06-2010' 
group by i.gCode, i.gName
Raj
  • 145
  • 6

1 Answers1

0

Have you checked out the PIVOT feature? You don't mention which SQL version you're using, but it's available in 2008, and does pretty much what you're after.

Also, your code seems awfully inefficient. You're doing selects inside selects fetching back data you already have access to. Maybe I'm having a hard time identifying with your data set, but it seems like a need for a case statement in your current situation, rather than lots of selects. I believe the following works:

select
  i.gName,
  case i.pSize when 0 then sum(Quantity) else 0 end as [0ml],
  case i.pSize when 180 then sum(Quantity) else 0 end as [180ml],
  case i.pSize when 375 then sum(Quantity) else 0 end as [375ml],
  { rest of case samples }
from
  saleslog as s
    inner join ItemGroup as i on s.pGroup = i.gGroup
where
  s.BillDate = '01-06-2010'
group by
  i.gName

I'm also a little confused on your question. You mention you have two tables, one has id, and iSize, but I don't see iSize, so I'm assuming pSize(?). Also, your sales table doesn't match up with your query, ie there is no quantity amount in your sales table, your create mentions [sales], but your actual query uses [salesdata].

Maybe some more clarifying?

Jon Angliss
  • 1,782
  • 10
  • 8
  • hey Jon Angliss i have edited my question as there were mistake. Actually i want to show ItemSize table column data(i.e. Size of drinks) as Column Header in my report and Sales quantity according to Drink Group(gName) in rows. – Raj Jul 06 '10 at 11:22
  • Your table structures still don't match your question, so I'm not sure where you're getting iSize from when your question doesn't use it, and you join on iGroup, but there is no iGroup field in the ItemGroup table. But, if the case statement above doesn't work for you, look at the PIVOT option in TSQL, it does exactly what you're after. – Jon Angliss Jul 06 '10 at 13:23
  • iSize Column is defined in ItemTable table, see its structure in above question – Raj Jul 07 '10 at 06:47
  • And iGroup is where? The answer is PIVOT if you can use it, or the CASE statement if you cannot. SELECT * FROM ( SELECT i.gName, case i.pSize when 0 then '0ml' when 180 then '180ml' when 375 then '375ml' end as [Size], Quantity FROM saleslog as s inner join ItemGroup as i on s.pGroup = i.gGroup WHERE s.BillDate = '01-06-2010' ) SalesInfo PIVOT ( SUM(Quantity) FOR [Size] IN ( [0ml],[180ml],[375ml] ) ) AS PivotTable ORDER BY [gName] I'm sure you can figure it out matching your fields. – Jon Angliss Jul 07 '10 at 07:36