Structure the Data horizontally in Sql

Question

Hi I have a query which returns data as below

select
    n.Key, np.value
from
    Test1 np
    join Test2 n on n.Key = np.Key
where
    n.NodeKey = 10000002 && np.pKey in (4,6,7,10,12)

which returns data as below

Key            value
--------       ------
10000002        2
10000002        0
10000002        2
10000002       True
10000002        1

Test2 is look up Table as below

  Key      PKey           Value                                                                                                                                                                                                                                                    
---------------------------------------
10000002     4               2                                                                                                                                                                                                                                                                
10000002     6               0                                                                                                                                                                                                                                                                
10000002     7               2                                                                                                                                                                                                                                                                
10000002     10              True
10000002     12              1

Wanted to change the query so that it should return data as below

NodeKey   Value1   Value2   Value3  Value4  Value5
--------------------------------------------------
10000002     2        0        2       True    1

Can you please help me with this.

https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Moudiz, Oct 25 '18 at 06:40
How is then `or` managed? In ANSI SQL `||` means concatenate. — jarlh, Oct 25 '18 at 06:46
`&&` is definately not T-SQL for `AND`. Simpy try `SELECT CASE WHEN 1 = 1 && 2 = 2 THEN 1 ELSE 0 END;` and you'll get a syntax error. I'm with what @jarlh appears to be suggsting here, I don't think the OP is using SQL Server. — Thom A, Oct 25 '18 at 07:18
@user1494438 sql server does not support the `||` operator (nor the `&&` operator for that matter). All logical operators in T-SQL are English words. — Zohar Peled, Oct 25 '18 at 07:21

Barbaros Özhan · Answer 1 · 2018-10-25T07:55:35.823

You may use pivot clause as :

with t as
(
select i.* from Test2 i 
) 
select [Key] as 'NodeKey', 
       [4]   as 'Value1',
       [6]   as 'Value2',
       [7]   as 'Value3',
       [10]  as 'Value4',
       [12]  as 'Value5'
  from t
 pivot
 (
  max(Value) for PKey in ([4], [6], [7], [10], [12])    
 ) q;

NodeKey  Value1 Value2  Value3  Value4  Value5
-------- ------ ------  ------  ------ -------
10000002    2     0       2      True     1

Rextester Demo

Sushil Sharma · Answer 2 · 2018-10-25T07:27:08.700

You can try using sql-server pivot For structuring data horizontally. Try below code..

Add another column using row_number() function and Store whole result in temp table

select
    n.Key, np.value,
    'Value'+convert(varchar(2),ROW_NUMBER() over (partition by [key] order by [key])) as tmpCol 
    into #tempTable --store result in tmp table
from
    Test1 np
    join Test2 n on n.Key = np.Key
where
    n.NodeKey = 10000002 && np.pKey in (4,6,7,10,12)

then use below pivot code.

select *
from 
(
  select [key], value,tmpCol
  from #tempTable
) src
pivot
(
  max(value)
  for tmpCol in (value1,value2,value3,value4,value5)
) piv;

Structure the Data horizontally in Sql

2 Answers2