8

I need to know whether 'pivot' in MS SQL can be used for converting rows to columns if there is no aggregate function to be used. i saw lot of examples with aggregate function only. my fields are string data type and i need to convert this row data to column data.This is why i wrote this question.i just did it with 'case'. Can anyone help me......Thanks in advance.

Taryn
  • 242,637
  • 56
  • 362
  • 405
Sivajith
  • 1,181
  • 5
  • 19
  • 38
  • 2
    You can always use min() or max() on varchar. There should only be one value per location in pivot grid. – Nikola Markovinović Jun 11 '12 at 08:34
  • thanks for the response.. see my problem is, say there is one employee and he has lot data in row wise. i need to select this data as a single row. so the problem is i cant use min() or max().... – Sivajith Jun 11 '12 at 08:42
  • Probably best if you supply example data and some desired results for that data so we can see what you are trying to do and why `MIN` / `MAX` won't work for you. – Martin Smith Jun 11 '12 at 08:51
  • ok.i will give u the data format. empid wagecode amount 1 basic 1000 1 TA 500 1 DA 500 2 Basic 1500 2 TA 750 2 DA 750 and i need the ans as empid BASic TA DA 1 1000 500 500 2 1500 750 750 – Sivajith Jun 11 '12 at 08:54

3 Answers3

15

You can use a PIVOT to perform this operation. When doing the PIVOT you can do it one of two ways, with a Static Pivot that you will code the rows to transform or a Dynamic Pivot which will create the list of columns at run-time:

Static Pivot (see SQL Fiddle with a Demo):

SELECT *
FROM
(
  select empid, wagecode, amount
  from t1
) x
pivot
(
  sum(amount)
  for wagecode in ([basic], [TA], [DA])
) p

Dynamic Pivot:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(wagecode) 
                  FROM t1 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT empid, ' + @cols + ' from 
             (
                 select empid, wagecode, amount
                 from t1
            ) x
            pivot 
            (
                sum(amount)
                for wagecode in (' + @cols + ')
            ) p '

execute(@query)

Both of these will give you the same results

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 1
    BTW - you can see you dynamic query here: http://sqlfiddle.com/#!3/88cd4/4 (I just had to adjust the query terminator so the declare segment wasn't executed separately from the rest of your code) – Jake Feasel Jun 11 '12 at 18:05
  • @JakeFeasel thanks for adding that, I didn't have time to do both fiddles this morning – Taryn Jun 11 '12 at 18:11
  • @bluefeet sure, no prob. The query terminator UI control is pretty new, wasn't sure if you knew about it / when it might be useful. – Jake Feasel Jun 11 '12 at 18:14
  • @JakeFeasel its very helpful, I wasn't aware that was added. – Taryn Jun 11 '12 at 18:17
1

sample format

empid     wagecode    amount
1              basic           1000
1              TA               500
1              DA               500
2              Basic           1500
2              TA               750
2              DA               750

empid      basic       TA        DA
1            1000         500      500
2            1500         750       750

THE ANSWER I GOT IS

   SELECT empID , [1bas] as basic, [1tasal] as TA,[1otsal] as DA
   FROM (
   SELECT empID, wage, amount
   FROM table) up
   PIVOT (SUM(amt) FOR wgcod IN ([1bas], [1tasal],[1otsal])) AS pvt
   ORDER BY empID 
   GO
Sivajith
  • 1,181
  • 5
  • 19
  • 38
0

Try this:

SELECT empid AS EmpID     
, ISNULL(SUM(CASE wagecode WHEN 'basic' THEN Amount ELSE 0 END), 0) AS Basic         
, ISNULL(SUM(CASE wagecode WHEN 'ta' THEN Amount ELSE 0 END), 0) AS TA     
, ISNULL(SUM(CASE wagecode WHEN 'da' THEN Amount ELSE 0 END), 0) AS DA 
FROM Employee
GROUP BY empid
vpv
  • 920
  • 2
  • 20
  • 46
  • @ V.P Verma :thanks for your replay. why i go for pivote is to reduce the complexity of the query and to reduce the execution time..i don't know exactly but i think pivote is better than case in this case..once again thanks for the replay..and want to know pivote or case is faster.. – Sivajith Jun 11 '12 at 12:07