I have this select as follows:
SELECT SUBSTRING(Col1, 1, 3) AS 'Series',
b.Col2,
CAST(c.Price * c.Qty AS MONEY) AS Total
FROM tableName a
inner join ....
...
WHERE DATE BETWEEN '1/1/2012' AND '1/31/2012'
AND B IN ( 'C50', 'C51', 'C52', 'C53', 'C54' )
GROUP BY Col1,
b.Col2,
c.Price,
c.Qty
Which returns this result set:
Series Col2 Total
---------- ---------- ----------
105 C50 5.00
105 C50 15.00
105 C53 20.00
105 C53 20.00
105 C53 20.00
And this is how I would like it to work:
Series C50 C53
---------- ---------- ----------
105 20.00 60.00
Not sure how to do the inner selects to get that result. Any ideas?
Please try the following codes. You have to use PIVOT query.
create table TableName (Series varchar(20),col2 varchar(10), price decimal(8,2))
insert into TableName values ('105','C50',30)
insert into TableName values ('105','C50',10)
insert into TableName values ('105','C53',20)
insert into TableName values ('105','C53',30)
SELECT Series,
[C50],
[C53]
FROM (SELECT Series,
col2,
price
FROM TableName) AS SourceTable
PIVOT ( SUM(price) FOR col2 IN ([C50],
[C53])
) AS PivotTable;
For potential null value for col2, please try the following code.
Note: you may need to adjust @PIV_COL size depend on how many distinct records of col2. I used 'NO_VAL' for potential NULL value of col2.
insert into TableName values ('105',NULL,100)
DECLARE @PIV_COL VARCHAR(100)
SELECT @PIV_COL = STUFF(
(
SELECT DISTINCT ',[' + ISNULL(COL2,'NO_VAL') + ']'
FROM TableName C
FOR XML PATH('')
), 1, 1, '')
EXEC
('
SELECT Series,' + @PIV_COL + '
FROM (
SELECT Series,
ISNULL(COL2,''NO_VAL'') col2,
price
FROM TableName ) AS SourceTable
PIVOT ( SUM(price) FOR col2 IN ('+ @PIV_COL + ')
) AS PivotTable
')
