Display row into column in SQL

Question

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.

If you don't have any idea how to write your query, you really need a tutorial. This site is for helping you when you get stuck. Not for writing the entire query for you. — Dale K, Oct 04 '21 at 05:36
You should have `ID = 3` for all 3 records of the `AttributeValue` table. — Tim Biegeleisen, Oct 04 '21 at 05:40
Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Charlieface, Oct 04 '21 at 10:49

score 1 · Answer 1 · answered Oct 04 '21 at 05:38

1

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.

answered Oct 04 '21 at 05:38

Tim Biegeleisen

502,043
27
286
360

i required dynamic query if new type is added in attribute type table that time query automatically add new column in output table. – pooja shah Oct 04 '21 at 07:39
You can't do that with regular SQL, you would need dynamic SQL (which is code which itself generates a SQL query). – Tim Biegeleisen Oct 04 '21 at 07:58

Gudwlk · Accepted Answer · 2021-10-05T11:36:19.040

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)

@poojashah if you need this dynamically, let me know ill add the code here for dynamic approach. I used static query as you have few attributes for the moment. — Gudwlk, Oct 04 '21 at 22:38
I need dynamic approach . in future new attribute type is added and i don't want to change in query that time — pooja shah, Oct 05 '21 at 06:18
@poojashah added Dynamic pivot as per your request. Please have a look and let me know, if you need further support. Happy to help :) — Gudwlk, Oct 05 '21 at 11:37

Display row into column in SQL

2 Answers2