CODEWITHSUNDEEP
sqlsql-server-2005
hype
hype

Reputation: 55

SQL query with return columns based on grouping

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?

Upvotes: 2

Views: 194

Answers (2)

Thit Lwin Oo
Thit Lwin Oo

Reputation: 3438

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
')

Upvotes: 0

Thit Lwin Oo
Thit Lwin Oo

Reputation: 3438

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;

Upvotes: 1

Related Questions