Transpose rows into columns dynamically in SQL Server

Question

I want to transpose the below rows dynamically into columns.

**Process Id     Attribute    Values**
1              Equipment     Normal
1              Complaints    No
1              Availability  30 min
2              Phone1        123456789
2              Phone2        987654321

I have tried to pivot it but I am unable to get the desired results. I need the below output

Process ID     Attribute1     Value1    Arrtibute2     Value2     Attribute3     Value3
1              Equipment     Normal       Complaints    No            Availability  30 min
2              Phone1        123456789    Phone2        987654321     NULL          NULL

One process can have one or more attributes. So if a process have 10 attributes, it should create 10 columns in the desired output. Any suggestions?

Hi Gordon, I am new to this platform. Please ignore my mistake. thanks — Jessica Yoskovich, Aug 11 '20 at 11:01
PIVOT after a ROW_NUMBER? Or get rid of that *Entity-Attribute-Vaule* table, e.g. by using JSON — dnoeth, Aug 11 '20 at 11:01
Have a look at [this question / answer](https://stackoverflow.com/questions/63068935/simple-sql-pivot/) combination for a very similar question. Update your question with your attempted solution if you still encounter problems. — Sander, Aug 11 '20 at 11:15
Hi dnoeth, i have tried to pivot using the row_number but the attribute value becomes the name of the column. I need the column names like attribute1, attribute2 ... — Jessica Yoskovich, Aug 11 '20 at 11:16
Based on your question I have given answer in dynamic query with PIVOT. It will provide desired output. — Soundappan A, Aug 17 '20 at 03:40

score 0 · Answer 1 · answered Aug 11 '20 at 11:17

All the newest ANSI compliant databases should support this. Witn no DBMS, I stick to the newest ANSI standard.

WITH
-- your input
input(Process_Id,Attribute,Values) AS (
          SELECT 1,'Equipment','Normal'
UNION ALL SELECT 1,'Complaints','No'
UNION ALL SELECT 1,'Availability','30 min'
UNION ALL SELECT 2,'Phone1','123456789'
UNION ALL SELECT 2,'Phone2','987654321'
)
,
-- need a sequence counter ...
with_seq AS (
SELECT
  ROW_NUMBER() OVER(PARTITION BY process_id) AS seq
, *
FROM input
)
SELECT
  process_id
, MAX(CASE seq WHEN 1 THEN attribute END) AS attrib1
, MAX(CASE seq WHEN 1 THEN values    END) AS val1
, MAX(CASE seq WHEN 2 THEN attribute END) AS attrib2
, MAX(CASE seq WHEN 2 THEN values    END) AS val2
, MAX(CASE seq WHEN 3 THEN attribute END) AS attrib3
, MAX(CASE seq WHEN 3 THEN values    END) AS val3
FROM with_seq
GROUP BY process_id;
-- out  process_id |  attrib1  |   val1    |  attrib2   |   val2    |   attrib3    |  val3 
-- out ------------+-----------+-----------+------------+-----------+--------------+-------
-- out           1 | Equipment | Normal    | Complaints | No        | Availability | 30 min
-- out           2 | Phone1    | 123456789 | Phone2     | 987654321 |              |

This is not very dynamic: adding a fourth attribute (question states that multiple attributes are possible) will break your solution. — Sander, Aug 11 '20 at 11:21
Hi Sander. That's exactly what i was about to say. Every time there is a new attribute, i will have to create a new column. I want this handled dynamically — Jessica Yoskovich, Aug 11 '20 at 11:24
In classic SQL, you can't have a dynamic number of columns. Pivoting to a variable number of columns is a SQL server specialty, in my opinion not very effective, and, especially , not standard SQL, so not portable to other database management systems. I use SQL generating SQL to create a quey of the type above dynamically- Google for the `PIVOT()` function in SQL server — marcothesane, Aug 11 '20 at 11:25

score 0 · Answer 2 · answered Aug 11 '20 at 14:29

Try this below code. It will provide desired output.

My sample Algorithm
 1. Create manual named columns by using ROW_NUMBER()
 2. Create Dynamic columns for Attribute,Value named as @columnsAttribute, @columnsValue
 3. Create Dynamic group by columns for both Attribute & Value named as @Allcolumns
 4. Dynamic query creation with pivot for both Attribute & Value

declare @tblAttribute as 
    table(processid int, 
    attribute NVARCHAR(100),
    value NVARCHAR(200))

insert into @tblAttribute(processid,attribute,value) 
values(1,'Equipment','Normal'),
        (1,'Complaints','No'),
        (1,'Availability','30 min'),
        (1,'test','testvalue'),
        (2,'Phone1','123456789'),
        (2,'Phone2','987654321')


;with ctetbl as
(
select ROW_NUMBER() over (partition by processid order by processid) rno,* from @tblAttribute
) 
,ctetbl1 as 
(
    select processid,
    'Attribute'+ cast(rno as NVARCHAR(2)) as DynamicAttribute,
    'Value'+cast(rno as NVARCHAR(2)) as DyanamicValue
    ,attribute,value from ctetbl
)
select * into #tblDynamicAttribute from ctetbl1

declare @Allcolumns as NVARCHAR(max),
        @columnsAttribute as NVARCHAR(max),
        @columnsValue as NVARCHAR(max),
        @sql as NVARCHAR(MAX) = ''


select @Allcolumns=coalesce(@Allcolumns+',','')+'max('+QUOTENAME(B.DynamicAttribute)+') as '+ QUOTENAME(B.DynamicAttribute) +',max('+QUOTENAME(B.DyanamicValue)+') as '+ QUOTENAME(B.DyanamicValue)
from (select distinct DynamicAttribute,DyanamicValue from #tblDynamicAttribute) as B
order by b.DynamicAttribute

select @columnsAttribute=coalesce(@columnsAttribute+',','')+QUOTENAME(B.DynamicAttribute)
from (select distinct DynamicAttribute,DyanamicValue from #tblDynamicAttribute) as B
order by b.DynamicAttribute

select @columnsValue=coalesce(@columnsValue+',','')+QUOTENAME(B.DyanamicValue)
from (select distinct DynamicAttribute,DyanamicValue from #tblDynamicAttribute) as B
order by b.DynamicAttribute


-- construct dynamic SQL
SET @sql ='
select x.processid,' + @Allcolumns +' from (
SELECT processid,' + @columnsAttribute+','+ @columnsValue +' FROM   
(
    SELECT 
        processid,attribute,value,dynamicattribute,DyanamicValue
    FROM 
        #tblDynamicAttribute p
) t 
PIVOT(
    max(attribute) 
    FOR dynamicattribute IN ('+ @columnsAttribute +')
) AS pivot_table
PIVOT(
    max(value) 
    FOR DyanamicValue IN ('+ @columnsValue +')
) AS pivot_table1

) x group by processid;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

drop table #tblDynamicAttribute

Sample Output

Transpose rows into columns dynamically in SQL Server

2 Answers2