Convert Rows to columns using 'Pivot' in mssql when columns are string data type

Question

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.

You can always use min() or max() on varchar. There should only be one value per location in pivot grid. — Nikola Markovinović
– Nikola Markovinović, Commented Jun 11, 2012 at 8: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
– Sivajith, Commented Jun 11, 2012 at 8: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
– Martin Smith, Commented Jun 11, 2012 at 8: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
– Sivajith, Commented Jun 11, 2012 at 8:54

Taryn · Accepted Answer · 2012-06-11 11:58:00Z

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

answered Jun 11, 2012 at 11:58

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jake Feasel Over a year ago

BTW - you can see you dynamic query here: 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)

Taryn Over a year ago

@JakeFeasel thanks for adding that, I didn't have time to do both fiddles this morning

Jake Feasel Over a year ago

@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.

Taryn Over a year ago

@JakeFeasel its very helpful, I wasn't aware that was added.

Sivajith · Accepted Answer · 2012-06-11 11:37:32Z

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

vpv · Accepted Answer · 2012-06-11 11:52:50Z

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

answered Jun 11, 2012 at 11:52

vpv

9482 gold badges21 silver badges47 bronze badges

1 Comment

Sivajith Over a year ago

@ 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..

Collectives™ on Stack Overflow

Convert Rows to columns using 'Pivot' in mssql when columns are string data type

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related