Pivoting the data

Question

This is my SQL statement

select  id , name, type,  value  from table1 a
INNER JOIN table2 b on a.id = b.id
where  b.type in ('display','contact','ship')

which produces below result

ID  name     type           value
5   test     display        display1
5   test     contact        contact1
5   test     ship           ship1
6   test2    display        display2
6   test2    contact        contact2
6   test2    ship           ship2

I need to get result in kind of pivoted format like this

id  name   display   contact   ship
5   test   display1  contact1 ship1
6   test2  display2  contact2 ship2

I tried this solution : https://stackoverflow.com/a/6849706/2645738 ,but its giving me the same result (3 rows for each data). It's like i need to group by id and name,but don't know how to make display,contact,ship as columns.

Would you please help me for the same.

Yogesh Sharma · Answer 1 · 2017-11-15T06:58:44.643

It is necessary to use PIVOT you could also do that by using simple case expression

SELECT ID,
      Name,
       MAX(CASE([type]) WHEN 'display' THEN value END) [display],
       MAX(CASE([type]) WHEN 'contact' THEN value END) [contact],
       MAX(CASE([type]) WHEN 'ship' THEN value END) [ship]
FROM <table> GROUP BY ID, Name

Result :

ID  Name    display     contact     ship
5   test    display1    contact1    ship1
6   test2   display2    contact2    ship2

score 2 · Accepted Answer · answered Nov 15 '17 at 06:49

2

This query should give you the results you want:

select  a.id , a.name, 
        max(case when b.type = 'display' then value end) as display,
        max(case when b.type = 'contact' then value end) as contact,
        max(case when b.type = 'ship' then value end) as ship
from table1 a
INNER JOIN table2 b on a.id = b.id
where  b.type in ('display','contact','ship')
group by a.id, a.name

answered Nov 15 '17 at 06:49

Giorgos Betsos

71,379
9
63
98

Thanks for your time. This worked. Can you change this in such a way that display,contact and ship are in one variable as comma separated values? e.g. DECLARE @Types varchar(max) = 'display,contact,ship'. I will be writing this code in stored procedure and this values may come in a single variable. – user2645738 Nov 15 '17 at 07:08

score 2 · Answer 3 · answered Nov 15 '17 at 06:50

This Worked for me

WITH T
AS
(
    SELECT
      id , 
      name, 
      type,  
      value  
      FROM table1 a
        INNER JOIN table2 b 
          ON a.id = b.id
        WHERE  b.type in ('display','contact','ship')
)
SELECT
  *
  FROM T
  PIVOT
  (
    MAX([Value])
    FOR
    [Type] IN
    (
        [display],[Contact],[Ship]
    )
  )PVT

Check the SQLFiddle

score 2 · Answer 4 · answered Nov 15 '17 at 06:59

If you want PIVOT:

DECLARE @DataSource TABLE
(   
    [id] TINYINT
   ,[name] VARCHAR(12)
   ,[type] VARCHAR(12)
   ,[value] VARCHAR(12)
);

INSERT INTO @DataSource ([id], [name], [type], [value])
VALUES (5, 'test', 'display', 'display1')
      ,(5, 'test', 'contact', 'contact1')
      ,(5, 'test', 'ship', 'ship1')
      ,(6, 'test2', 'display', 'display2')
      ,(6, 'test2', 'contact', 'contact2')
      ,(6, 'test2', 'ship',  'ship2');

SELECT *
FROM @DataSource
PIVOT
(
    MAX([value]) FOR [type] IN ([display], [contact], [ship])
) PVT;

score 0 · Answer 5 · edited Nov 17 '17 at 04:10

0

select id,name,[display],[contact],[ship]

from #b 

pivot(max(value) for type in([display],[contact],[ship])) As d

edited Nov 17 '17 at 04:10

Yogesh Sharma

49,870
5
26
52

answered Nov 16 '17 at 06:40

sanjay singh

1
3

2

A code only answer does not provide much to the OP or anyone finding this answer. Please update your answer and expand on why you believe this answers the OP's question. – Nov 17 '17 at 04:17
please look at [answer] – JimHawkins Nov 17 '17 at 07:56

Pivoting the data

5 Answers5