Combine multiple SQL fields into 1 output row

Question

Having a SQL table like

UserID |Attribute | Value
1 |Username | Marius
1 |Password | Fubar

I want to create an output like:

1 | Marius | Fubar

Maybe I'm just too tired to see it, doesn't sound too complicated, but I just can't seem to figure it out. Any help is appreciated.

Answer 1

Edited:

One column per result:

If you try to combine multiple Attributes with the same UserID it would be something like this:

Short query (one UserID):

declare @concattedtext varchar(1000)

SELECT @concattedtext=coalesce(@concattedtext + '|', '') + Value
FROM #users WHERE UserID=1

SELECT @concattedtext

RESULT with your example data:

1 | Marius | Fubar

Full query (all UserID's)

-- Your source table
CREATE Table #users (UserID int, Attribute varchar(50), Value varchar(50))
-- some entries
INSERT into #users (UserID, Attribute, Value)
VALUES (1, 'Test1', 'attr1')
INSERT into #users (UserID, Attribute, Value)
VALUES (1, 'Test2', 'attr2')
INSERT into #users (UserID, Attribute, Value)
VALUES (1, 'Test3', 'attr3')
INSERT into #users (UserID, Attribute, Value)
VALUES (2, 'Test4', 'attr4')

-- ids table variable (for distinct UserID's)
DECLARE @ids TABLE
(
 rownum int IDENTITY (1, 1) Primary key NOT NULL,
 UserID int
)

-- Output table variable
DECLARE @out TABLE
(
 rownum int IDENTITY (1, 1) Primary key NOT NULL,
 UserID int,
 ConcatText varchar(1000)
)

-- get distinct id's
INSERT INTO @ids(UserID)
SELECT DISTINCT(UserID) FROM #users

-- Foreach vars
declare @RowCnt int
declare @MaxRows int
select @RowCnt = 1
select @MaxRows=count(*) from @ids

-- UserID
declare @id int
declare @concattedtext varchar(1000)

-- process each id
while @RowCnt <= @MaxRows
begin
 SET @id = 0

 SELECT @id=UserID
 FROM @ids WHERE rownum=@RowCnt

 SET @concattedtext = CONVERT(nvarchar(50), @id)
 FROM @ids WHERE rownum=@RowCnt

 SELECT @concattedtext=coalesce(@concattedtext + '|', '') + Value
 FROM #users WHERE UserID=@id

 INSERT INTO @out(UserID, ConcatText)
 VALUES (@id, @concattedtext)

 -- next UserID
 Select @RowCnt = @RowCnt + 1
end

SELECT * FROM @out

DROP TABLE #users

Result:

rownum|UserID|ConcatTex
1     | 1    |1|attr1|attr2|attr3
2     | 2    |2|attr4
DROP TABLE #users

You might need a sort field to get your parameters in your requested order.

Multiple columns

Your data needs to have an equal count of attributes if you like to get a table with multiple columns. And you still need to take care about the ordering.

In this case a hardcoded query with a group by would be your choice.

Answer 2

Would something like...

SELECT userID,
       GROUP_CONCAT (Value SEPARATOR '|')
    FROM my_table
    GROUP BY UserID;

be what you are looking for?

Answer 3

Why don't you use self joins, ie. :

select u1.userid, u1.value, u2.value 
from yourtable u1
inner join yourtable u2 on u2.userid=u1.userid
where u1.attribute='Username' and u2.attribute='Password';

Answer 4

If you keep your data that way, you will need to do a crosstab query and pivot your rows into columns. If you are using Microsoft SQL Server, check out "crosstab query" and the pivot operator in Books Online.