CODEWITHSUNDEEP
sqlsql-server
Hanzou
Hanzou

Reputation: 73

SQL Server query result by column to row

This is my sample code:

SQL Fiddle

I need it to result like this:

category    outlet1     outlet2     outlet3
Sale        70          20          40
Expense     250         130         200

How can I do this?

EDIT: My outlets are not fixed, sorry for not telling this beforehand.

Upvotes: 0

Views: 53

Answers (3)

Squirrel
Squirrel

Reputation: 24763

you may also use the PIVOT operator

SELECT *
FROM
(
    SELECT category, outletname, amt
    FROM   tblcategory c
    INNER JOIN tbltran t ON t.catid = c.id
    INNER JOIN tbloutlet o ON o.id = t.outletid
) d
PIVOT
(
     SUM(amt)
     FOR outletname in ([Outlet1] , [Outlet2] , [Outlet3])
) p

EDIT : below is the Dynamic SQL version

declare @Outlets nvarchar(max),
    @SQL    nvarchar(max)

select  @Outlets = isnull(@Outlets + ',', '') + quotename(outlet)
from    outlet

select  @SQL = '
SELECT *
FROM
(
    SELECT category, outletname, amt
    FROM   tblcategory c
    INNER JOIN tbltran t ON t.catid = c.id
    INNER JOIN tbloutlet o ON o.id = t.outletid
) d
PIVOT
(
     SUM(amt)
     FOR outletname in (' + @Outlets + ')
) p'

print   @SQL  -- print out for verification
exec    sp_executesql @SQL

Upvotes: 0

Mark
Mark

Reputation: 418

Here with dynamic Outlets http://sqlfiddle.com/#!18/a7b09/25

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(outletname) 
                    from tbloutlet
                    group by outletname
                    order by outletname
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT category,' + @cols + ' from 
             (
                SELECT c.category, o.outletname, SUM(t.amt) as amt
                FROM tblcategory c
                INNER JOIN tbltran t ON t.catid = c.id
                INNER JOIN tbloutlet o ON o.id = t.outletid
                GROUP BY c.category, o.outletname
            ) x
            pivot 
            (
                sum(amt)
                for outletname in (' + @cols + ')
            ) p '

execute(@query);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can solve your particular problem using conditional aggregation:

SELECT c.category,
       SUM(CASE WHEN o.outletname = 'Outlet1' THEN t.amt ELSE 0 END) as Outlet1,
       SUM(CASE WHEN o.outletname = 'Outlet2' THEN t.amt ELSE 0 END) as Outlet2,
       SUM(CASE WHEN o.outletname = 'Outlet3' THEN t.amt ELSE 0 END) as Outlet3
FROM tblcategory c INNER JOIN
     tbltran t
     ON t.catid = c.id INNER JOIN
     tbloutlet o
     ON o.id = t.outletid
GROUP BY c.category;

If the outlet names are not fixed, then you need dynamic SQL. The problem cannot be solve using a single SELECT query.

Upvotes: 1

Related Questions