query with a column per row of a linked table - dynamic sql

I've thought about this a bit more and relised that it can be done using
dynamic SQL, but I would like to avoid this if possible. What are the other
options?
"WCL" <WCL@.nospam.nospam> wrote in message
news:usRAcZGIGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Is it possible to have query result to have a column per row of a table?
>
> e.g.
> Employees table
> ID (identity)
> FirstName
> Ref
>
> containing
> ID, FirstName
> 1 Tom
> 2 Dick
> 3 Harry
>
>
> Project table
> ID (identity)
> Name
>
> containing
> ID, Name
> 1 Client A
> 2 Client B
> 3 Client C
>
> Timesheets
> ID (identity)
> Employees_ID
> Project_ID
> Hours
>
> containing 6 rows
> ID
> Employee_ID
> Project_ID
> Hours
> 1
> 1
> 1
> 5
> 2
> 1
> 2
> 15
> 3
> 2
> 1
> 2
> 4
> 2
> 2
> 4
> 5
> 3
> 1
> 8
> 6
> 3
> 2
> 8
>
> NB - No records for client C
>
> I can get three columns (name, client, hours) with nine rows no problem,
> but how do I get 3 rows with a column per Client, like this:?
>
> Name
> Client A
> Client B
> Client C
> Tom
> 5
> 15
> 0 or NULL
> Dick
> 2
> 4
> 0 or NULL
> Harry
> 8
> 8
> 0 or NULL
>
>
>No, it is not really useful from a SQL standpoint to do this, so it is not a
part of SQL (columns should contain like things, not multiple different
things.)
I think you can do it using several UNION ALLs:
SELECT client, cast(client as varchar(30)), 1 as sorting
FROM table
UNION ALL
SELECT client,cast(value1 as varchar(30)), 2 as sorting
FROM table
order by client, sorting --maybe something different than key
I would suggest using the client tool to format data like this.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"WCL" <WCL@.nospam.nospam> wrote in message
news:u4q6ipGIGHA.3176@.TK2MSFTNGP12.phx.gbl...
> I've thought about this a bit more and relised that it can be done using
> dynamic SQL, but I would like to avoid this if possible. What are the
> other options?
> "WCL" <WCL@.nospam.nospam> wrote in message
> news:usRAcZGIGHA.1312@.TK2MSFTNGP09.phx.gbl...
>