Need a query...

Hello,
I have a table like that:
ID | Date | Value
---
1 2004-12-01 12
1 2004-12-02 13
1 2004-12-03 11
2 2004-12-01 23
2 2004-12-02 21
2 2004-12-03 28
I need to create a SQL query to get a recordset like this:
ID | '2004-12-01' | '2004-12-02' | '2004-12-03' |
---
1 12 13 11
2 23 21 28
Thanks,
GBHello GB,
There are ways to create the end result you're looking for...but I'm not
quite sure how scalable it is. You're essentially going to have to craft a
sql statement dynamically in order to get your desired results.
I see lots of looping in code to create this.
What are you trying to accomplish with a recordset like this? If it's a
reporting purpose I do know that Crystal Reports will take vertical set of
data like your originating table and turn it into a horizontal format like
your desired end result.
If this is the case, I can check with one of my team members on how to do
this (he does this for us in Crystal Reports).
At any rate, here's an example of how a sql statement can be constructed to
create your end result (hopefully someone can come up with a better
solution).
DECLARE @.tbl TABLE
(
[ID] INT,
[Date] DATETIME,
Value INT
)
INSERT INTO @.tbl ([ID], [Date], Value)
VALUES(1, '2004-12-01', 12);
INSERT INTO @.tbl ([ID], [Date], Value)
VALUES(1, '2004-12-02', 13);
INSERT INTO @.tbl ([ID], [Date], Value)
VALUES(1, '2004-12-03', 11);
INSERT INTO @.tbl ([ID], [Date], Value)
VALUES(2, '2004-12-01', 23);
INSERT INTO @.tbl ([ID], [Date], Value)
VALUES(2, '2004-12-02',21);
INSERT INTO @.tbl ([ID], [Date], Value)
VALUES(2, '2004-12-03', 28);
SELECT
MAIN.[ID],
ISNULL(D2004_12_01.Value, 0) AS [2004-12-01],
ISNULL(D2004_12_02.Value, 0) AS [2004-12-02],
ISNULL(D2004_12_03.Value, 0) AS [2004-12-03]
FROM
@.tbl AS MAIN
LEFT OUTER JOIN @.tbl AS D2004_12_01
ON D2004_12_01.[Date] = '2004-12-01'
AND D2004_12_01.[ID] = MAIN.[ID]
LEFT OUTER JOIN @.tbl AS D2004_12_02
ON D2004_12_02.[Date] = '2004-12-02'
AND D2004_12_02.[ID] = MAIN.[ID]
LEFT OUTER JOIN @.tbl AS D2004_12_03
ON D2004_12_03.[Date] = '2004-12-03'
AND D2004_12_03.[ID] = MAIN.[ID]
GROUP BY
MAIN.[ID],
D2004_12_01.Value,
D2004_12_02.Value,
D2004_12_03.Value
Good luck,
Chris
"GB" <v7v1k3@.hotmail.com> wrote in message
news:0GgJd.1716$Ob.1098@.edtnps84...
> Hello,
> I have a table like that:
> ID | Date | Value
> ---
> 1 2004-12-01 12
> 1 2004-12-02 13
> 1 2004-12-03 11
> 2 2004-12-01 23
> 2 2004-12-02 21
> 2 2004-12-03 28
> I need to create a SQL query to get a recordset like this:
> ID | '2004-12-01' | '2004-12-02' | '2004-12-03' |
> ---
> 1 12 13 11
> 2 23 21 28
>
> Thanks,
> GB
>|||You could probably do it yourself with RAC :)
www.rac4sql.net|||Oh, that looks pretty interesting...
I have to use that...
Thanks!
"Pike" <stevenospam@.rac4sql.net> wrote in message
news:u5yLoJqAFHA.2640@.TK2MSFTNGP14.phx.gbl...
> You could probably do it yourself with RAC :)
> www.rac4sql.net
>