transposing rows into cols without using agg function

Question

Hi there,

I want to get the same number of rows into columns "as is" without defining values in the relationship between both.

DECLARE @t1 AS TABLE(Id2 tinyint, concept varchar(5))

insert into @t1(Id2, concept)

values (1,'abc'),(2,'abc2'),(3,'def'),(4,'nil'),(5,'chk')

select * from @t1

desireout outcome would be the following:

Ideally the code should be dynamic as this is only a POC and the definitive topic contains about 60 rows..

Answer 1

Hi @Northface
Please check this:

DECLARE @sql_str NVARCHAR(MAX)  
DECLARE @spread_elements NVARCHAR(MAX)  
          
SELECT @spread_elements = ISNULL(@spread_elements + ',','') + QUOTENAME(concept)   
FROM #t1   
GROUP BY concept  
  
SELECT @sql_str = 'select t1.concept,x.* from(select concept,''''as col from #t1)t PIVOT(max(col) for concept in ( ' + @spread_elements + ')) x   
                         cross join #t1 t1 order by concept'  
          
PRINT @sql_str  
EXECUTE sp_executeSQL @sql_str;  

Best regards,
LiHong

Answer 2

Ideally the code should be dynamic as this is only a POC and the definitive topic contains about 60 rows..

That would be only possible with dynamic SQL and that means a lot of coding.

A fix defined list of values can be easily achived with a PIVOT query: FROM - Using PIVOT and UNPIVOT

Answer 3

Try
DROP TABLE IF EXISTS #t1
CREATE table #t1(Id2 tinyint, concept varchar(5))

insert into #t1(Id2, concept)

values (1,'abc'),(2,'abc2'),(3,'def'),(4,'nil'),(5,'chk');

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(quotename (x.concept),',')  WITHIN GROUP (ORDER BY x.concept) FROM (SELECT DISTINCT concept FROM #t1) x


PRINT @cols;

SELECT @sql = 'select * from #t1 PIVOT(max(id2) for concept in ( ' + @cols + ')) x'

PRINT @sql
EXECUTE sp_executeSQL @sql;

Answer 4

DROP TABLE IF EXISTS #t1  
CREATE table #t1(Id2 tinyint, concept varchar(5))  
insert into #t1(Id2, concept)  
      
 values (1,'abc'),(2,'abc2'),(3,'def'),(4,'nil'),(5,'chk');  
      
 DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);  
      
 SELECT @cols = STRING_AGG(quotename (x.concept),',')  WITHIN GROUP (ORDER BY x.concept) FROM (SELECT DISTINCT concept FROM #t1) x  
      
      
 PRINT @cols;  
      
 SELECT @sql = 'select t1.concept, x.* from #t1 PIVOT(max(id2) for concept in ( ' + @cols + ')) x cross join #t1 t1 order by concept'  
      
 PRINT @sql  
 EXECUTE sp_executeSQL @sql;  

Tom