How to to select two columns (Name, Value) and return as single result?

Question

So I have a table that has a column for Name, and a column for Value and an ID. There can be multiple rows for the same ID. I would like to create a select that will return a single row for each ID and the values in the Name column would be the column name, and the Value would be the value. Example:

CREATE TABLE dbo.Attribute
(
    AttributeID int NOT NULL,
    Name varchar(20) NOT NULL,

    Value varchar(20) NOT NULL
   ) ;

Data:

{1,"Color", "Blue"},{1,"Material", "leather"}

Would like Select to return:

[AttributeID:1, Color:Blue, Material: leather]

I have been playing with PIVOT and UNPIVOT but not getting what I need.

You may wish to do some research on the advantages and disadvantages of an EAV database model. — Nick.Mc, Feb 04 '15 at 21:11
Yes Nick! I found this to be excellent: http://www.sturnus.co.uk/performance/2008-07/the-curse-of-the-name-value-pair/ — n8wrl, Feb 04 '15 at 21:14
Especially the disadvantages (like ability to query). This is a horrible way to store data. — HLGEM, Feb 04 '15 at 21:15
We have NO idea of what names and values we will be getting for this case. This is the current architecture. Can you please elaborate on the dynamic pivot, as I have not been able to get this to work. — CSharpAtl, Feb 04 '15 at 21:19
I understand the query limitations, I am actually working on moving this data to an elastic search solution, and working on process to query the relational data and move it to non relational for the search service. — CSharpAtl, Feb 04 '15 at 21:21
Here is an example http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query It gathers all the names in a query so you dont have to write them manually. — Mihai, Feb 04 '15 at 21:22
@Mihai, add this link as an answer, I will mark it as the answer. Thank you. — CSharpAtl, Feb 04 '15 at 21:32
Eh its just a link.Work out an answer to your question and post it here,I`ll upvote it. — Mihai, Feb 04 '15 at 21:35
HAHA, your link was exactly what I needed. I was working with something similar, but could not get it to work. You link was perfect...so thank you. — CSharpAtl, Feb 04 '15 at 21:45
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/70267/discussion-between-csharpatl-and-mihai). — CSharpAtl, Feb 04 '15 at 21:53

score 1 · Answer 1 · edited Feb 05 '15 at 00:00

Thanks to @Mihai's link. I was able to do what I

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.Name) 
            FROM VariantAttribute c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT variantid, ' + @cols + ' from 
            (
                select variantid
                    , Name
                    , value
                from VariantAttribute
           ) x
            pivot 
            (
                 max([Value])
                for Name in (' + @cols + ')
            ) p '


execute(@query)

Before I get more slack about how I am storing this data. There is not a way out of it at the moment, but this query is to push this data to Azure Search (uses Elastic search) to be able to easily search on this data.

How to to select two columns (Name, Value) and return as single result?

1 Answers1