Combining multiple SQL fields into 1 line of output

Question

Having an SQL table, for example

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

I want to create output like:

1 | Marius | Fubar

Maybe I'm just too tired to see it, it does not seem too complicated, but I just can not understand it. Any help is appreciated.

Answer 1

Why don't you use self-join, i.e.

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 2

There will be something like ...

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

be what you are looking for?

Answer 3

:

:

UserID, :

( ):

declare @concattedtext varchar(1000)

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

SELECT @concattedtext

:

1 | Marius | Fubar

( )

-- 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

:

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

.

, . .

.

Answer 4

, - . Microsoft SQL Server, "- " pivot Books Online.