t sql horizontal query

Question

Hi,

I have a sql table like this. I need to make this table horizontally. Their properties will be written side by side. Descriptions will be written below. How can I do that. I would be glad if you help.

select top 100 ASP.Articel AS [Product Name], ASPD.English AS Ozellik, ASP.English  
  
from ArticelSpec ASP  
  
inner join ArticelSpecDefinition ASPD on ASP.ArticelSpecDefinition = ASPD.Oid  
  
where Articel='176E0AC3-1874-4801-BEB8-16E9B2F7D1FD'   

Answer 1

Do you want just these 5 columns with these pre-defined titles or you want it to be dynamic?

For simple static PIVOT it would be

;with cte as (select top 100 ASP.Articel AS [Product Name], ASPD.English AS Ozellik, ASP.English  
      
 from ArticelSpec ASP  
      
 inner join ArticelSpecDefinition ASPD on ASP.ArticelSpecDefinition = ASPD.Oid  
      
 where Articel='176E0AC3-1874-4801-BEB8-16E9B2F7D1FD'  order by ASP.Articel)  
  
select * from cte PIVOT (max(English) FOR Ozelik IN ([Number of Pockets], [Sleeve Detail], ...)) pvt  

Answer 2

What is your desired output? I believe you're looking at dynamic PIVOT for your problem. Search for 'dynamic pivot' for solution implementation. This assumes that Ozelik colum values are less than 128 characters.

Answer 3

Hi,

This is the output I wanted. Can you make an example by looking at the table and the code?

Answer 4

select ASP.Articel AS [Product Name],   
max(Case when ASPD.English ='NUMBER OF POCKETS' then  ASP.English  end ) as [NUMBER OF POCKETS],   
max(Case when ASPD.English ='SLEEVE DETAIL' then  ASP.English  end ) as [SLEEVE DETAIL],   
max(Case when ASPD.English ='PRODUCT WEIGHT' then  ASP.English  end ) [PRODUCT WEIGHT],   
max(Case when ASPD.English ='WARRANTY' then  ASP.English  end ) [Number of Pockets],   
max(Case when ASPD.English ='EXTRA PRODUCT DETAIL 1' then  ASP.English  end ) [EXTRA PRODUCT DETAIL 1]    
--.....  
from ArticelSpec ASP          
inner join ArticelSpecDefinition ASPD on ASP.ArticelSpecDefinition = ASPD.Oid  
where Articel='176E0AC3-1874-4801-BEB8-16E9B2F7D1FD'  
Group by ASP.Articel  

Answer 5

Hi @Ali Yılmaz
For static PIVOT query, please refer to Naomi and JingyangLi's answers which you need to type column names manually.
While in this issue, you could try dynamic PIVOT if there are too many columns you want to display.
Check this:

DECLARE @sql_str VARCHAR(MAX)  
DECLARE @spread_elements VARCHAR(MAX)  
DECLARE @Article VARCHAR(MAX)  
SET @Article = '176E0AC3-1874-4801-BEB8-16E9B2F7D1FD'   
  
SELECT @spread_elements = ISNULL(@spread_elements + ',','') + QUOTENAME(ASPD.English)   
FROM ArticelSpec ASP inner join ArticelSpecDefinition ASPD ON ASP.ArticelSpecDefinition = ASPD.Oid  
WHERE Articel=@Article  
GROUP BY ASPD.English  
--PRINT @spread_elements  
  
SET @sql_str = '  
SELECT * FROM   
(  
 select top 100 ASP.Articel AS [Product Name], ASPD.English AS Ozellik, ASP.English  
 from ArticelSpec ASP inner join ArticelSpecDefinition ASPD on ASP.ArticelSpecDefinition = ASPD.Oid  
 where Articel='+@Article+'  
)S   
PIVOT (MAX(English) FOR Ozellik IN ( '+ @spread_elements +') ) AS P   
ORDER BY [Product Name]  
'  
--PRINT (@sql_str)  
EXEC (@sql_str)  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.