Crosstab, Pivot Query representation

Hello,

I need help with data representation.

I have a query :

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

The above query returns the following results:
USERLOGIN SOURCE DBUSERNAME

Don Crilly FC8 Don Crilly
Don Crilly ACT Donald Crilly
Don Crilly SFS Don Crilly

I need the output in following format:

USERLOGIN ACT FC8 SFS
-
Don Crilly Donald Crilly Don Crilly Don Crilly

Can you please guide me as to what I should do to achive the required results.

Thanks.

If you use SQL Server 2005:
SELECT * FROM (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) p
PIVOT (MIN(DBUSERNAME) FOR SOURCE IN ([FC8], [ACT], [SFS])) pvt
--Or You can use the following for earlier versions
select a.USERLOGIN
, min(case a.SOURCE when 'FC8' then DBUSERNAME end) as [FC8]
, min(case a.SOURCE when 'ACT' then DBUSERNAME end) as [ACT]
, min(case a.SOURCE when 'SFS' then DBUSERNAME end) as [SFS]
from (

SELECT USERLOGIN, SOURCE, DBUSERNAME FROM TBL_USER
WHERE USERLOGIN LIKE 'Don Crilly'

) as a
group by a.USERLOGIN|||

If you're using SQL Server 2005, then you can use the PIVOT operator to do this.

For example, this will work for your query:

SELECT *

FROM (SELECT USERLOGIN, SOURCE, DBUSERNAME

FROM TBL_USER

WHERE USERLOGIN LIKE 'Don Crilly'

) SOURCEQUERY

PIVOT (

MIN(DBUSERNAME)

FOR SOURCE IN ([ACT], [FC8], [SFS])

) AS PIVOTTABLE

The restrictions for using the PIVOT operator are that the PIVOT clause must include an aggregate operator (the MIN in this case, which will work unless a single USERLOGIN and SOURCE combination can have multiple DBUSERNAMEs associated with it) and that the resulting column names (ACT, FC8 and SFS) must be listed explicitly (unlike MS Excel, for example, which just creates columns for every value in the pivotting column).

Hope that gives you a start at least.

Iain

|||

This query will do..

Select * from dbo.TBL_USER
PIVOT
(
Min(DBUserName) For Source in ([FC8],[ACT],[SFS])
) as T
Where UserLogin = 'Don Crilly'

|||Thanks a lot :)