SQL Transpose table - sqlserver

Question

i have the following table

create table mytab (
  mID int primary key,
  pname varchar(100) not null,
  pvalue varchar(100) not null
)

example data looks like

mID     |pname  |pvalue
-----------------------
1       |AAR    |   2.3
1       |AAM    |   1.2
1       |GXX    |     5
2       |AAR    |   5.4
2       |AAM    |   3.0
3       |AAR    |   0.2

I want to flip the table so that i get

mID     | AAR   |   AAM |    GXX|
---------------------------------
1       | 2.3   |   1.2 |      5|
2       | 5.4   |   3.0 |      0|
3       | 0.2   |     0 |      0

Is this somehow possible and if so, is there a way to create a dynamic query because there are lots of these pname pvalue pairs

http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — bvr, Jul 22 '14 at 07:41

Deepshikha · Accepted Answer · 2014-07-22T07:52:25.387

1

Write Dynamic Pivot Query as:

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

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


select @colsFinal =  STUFF((SELECT distinct ',' +
                        'ISNULL('+QUOTENAME(pname)+',0) AS '+ QUOTENAME(pname)
                      FROM mytab
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '')
--Edited query to replace null with 0 in Final result set.
SELECT @query = 'SELECT mID, '+@colsFinal +'
FROM mytab
PIVOT
(
  MAX(pvalue)
  FOR pname IN(' + @cols + ')) AS p;'


exec sp_executesql @query

Check demo here..

edited Jul 22 '14 at 07:52

answered Jul 22 '14 at 07:42

Deepshikha

9,896
2
21
21

execute(@query) missing.. – bvr Jul 22 '14 at 07:44
@bvr thanks .. I've added that along with Demo link.. – Deepshikha Jul 22 '14 at 07:54
works so far, problem is i have several lines with the same mID. I wanna have one mID per row – tuxmania Jul 22 '14 at 08:17
Well that shouldn't be the case as Pivoting is already grouping on basis of mID column.. please post the row data which is creating the issue – Deepshikha Jul 22 '14 at 08:31

score 0 · Answer 2 · edited May 23 '17 at 11:50

0

Use pivot as following format:

select *
From (select *
     From mytab)p
PIVOT(SUM(pvalue) FOR pname IN ([AAR],[AAM],[GXX]))pvt

In order to dynamic PIVOT use flowing reference:

Dynamic PIVOT Sample1

Dynamic PIVOT Sample2

edited May 23 '17 at 11:50

Community

1
1

answered Jul 22 '14 at 07:38

mehdi lotfi

11,194
18
82
128

your statement has a syntax error. paren is missing, and even if i close it, it won't work – tuxmania Jul 22 '14 at 07:42
I edit my post. please check again. – mehdi lotfi Jul 22 '14 at 07:49

score 0 · Answer 3 · answered Jul 22 '14 at 08:52

Declare @t table (mID INT, pname VARCHAR(10),  pvalue  FLOAT)
INSERT INTO @t (mID,pname,pvalue)values (1,'AAR',2.3)
INSERT INTO @t (mID,pname,pvalue)values (1,'AAM',  1.2)
INSERT INTO @t (mID,pname,pvalue)values (1,'GXX',  5)
INSERT INTO @t (mID,pname,pvalue)values (2,'AAR',  5.4)
INSERT INTO @t (mID,pname,pvalue)values (2,'AAM',  0.3)
INSERT INTO @t (mID,pname,pvalue)values (3,'AAR',  0.2)
select  mid,
CASE WHEN [AAR]IS NOT NULL THEN [AAR] ELSE ISNULL([AAR],0)END [AAR], 
CASE WHEN [AAM]IS NOT NULL THEN [AAM] ELSE ISNULL([AAM],0)END [AAM], 
CASE WHEN [GXX]IS NOT NULL THEN [GXX] ELSE ISNULL([GXX],0)END [GXX]
from
(
  select mID, pvalue,pname
  from @t
) d
pivot
(
  max(pvalue)
  for pname in ( [AAR], [AAM], [GXX])
) piv;

SQL Transpose table - sqlserver

3 Answers3