I have Two SQL Table

1st Table Name :- AttributeType

2nd Table Name :- AttributeValue

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

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

Thanks in advance.

CodePudding user response：

We can use pivoting logic here:

SELECT
    av.ID,
    MAX(CASE WHEN at.Name = 'Name'    THEN av.Value END) AS Name,
    MAX(CASE WHEN at.Name = 'Address' THEN av.Value END) AS Address,
    MAX(CASE WHEN at.Name = 'Amount'  THEN av.Value END) AS Amount
FROM AttributeValue av
INNER JOIN AttributeType at
    ON at.ID = av.AttributeId
GROUP BY
    av.ID;

Note: There appears to be a type in your AttributeValue table. In order for this to work, all ID values for a given person should have the same value. In this case, all three sample records should have an ID value of 1.

CodePudding user response：

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