sqlsql-servert-sqldynamicdynamicquery

Display row into column in SQL

I have Two SQL Table

1st Table Name :- AttributeType

ID Name
1 Name
2 Address
3 Amount

2nd Table Name :- AttributeValue

ID AttributeId Value
1 1 John
2 2 Ohio,USA
3 3 500$

I want to combine this two table and join with other table column name will be display as per value in Attribute type table (if there is 5 rows, 5 column will be display in output table)

Output would be Output table

ID Name Address Amount
1 John Ohio,USA 500$

I don't have idea about how to write query Can anyone help me?

Thanks in advance.

Solution

  • I have added some changes to the data given above. Your Table column name and value is same which is a problem when using pivoting logic. So I changed it. ID id taken indirectly using rownumber. if we use id provided in the table, it will not give you proper result.

    create table AttributeType
(ID int
,[AttName] varchar(100) --Changed to [AttName]
)
insert into AttributeType values  (1, 'Name')
 ,(2,'Address')
 ,(3, 'Amount')

Create table AttributeValue
(
 ID int 
, AttributeId int
, [Value] varchar(100)
)
insert into AttributeValue values (1,1,'John')
,(2,2, 'Ohio,USA')
,(3,3, '500$')


 SELECT 
 ROW_NUMBER() OVER (ORDER BY [Name]) as ID ,[Name], address, Amount FROM
 (

 SELECT
  [a].[AttName] 
  ,[av].[Value]
 FROM AttributeValue av
 INNER JOIN AttributeType [a]
    ON a.ID = av.AttributeId

   ) as [sourceTable]
   PIVOT
    (
     MAX([sourceTable].[Value] ) FOR  [sourceTable].[AttName]  IN ([Name], [Address],[Amount])
     ) AS pivoted

    Dynamic Approach

      DECLARE @cols AS NVARCHAR(MAX),
  @query  AS NVARCHAR(MAX);

  SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(a.[AttName]) 
        FROM  AttributeType [a]
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

     set @query = 'SELECT ROW_NUMBER() OVER (ORDER BY [Name]) as ID , ' + @cols + ' from 
          (
            SELECT
             [a].[AttName]   
             ,[av].[Value]
            FROM AttributeValue av
                INNER JOIN AttributeType [a]
                 ON a.ID = av.AttributeId
       ) as [sourceTable]
        pivot 
        (
            MAX([sourceTable].[Value] ) FOR
            [sourceTable].[AttName] in (' + @cols + ')
        ) as pivoted '


   execute(@query)

    Result