How to get output ( formatted)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to get output ( formatted)

Author

Topic

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-14 : 04:52:34

Hi all,
my table having the date in following way


R_ID	R_Date	R_Value
1	10-Mar-07	10
1	11-Mar-07	1
1	12-Mar-07	11
1	14-Mar-07	23
1	17-Mar-07	44
1	18-Mar-07	23
1	19-Mar-07	54
1	20-Mar-07	24
1	21-Mar-07	66
1	22-Mar-07	34
1	23-Mar-07	25
1	24-Mar-07	33
1	25-Mar-07	67
1	26-Mar-07	35
2	10-Mar-07	44
2	11-Mar-07	555
2	12-Mar-07	64
2	14-Mar-07	35
2	17-Mar-07	7765
2	18-Mar-07	354
2	19-Mar-07	35
2	20-Mar-07	66
2	21-Mar-07	53
2	22-Mar-07	567
2	23-Mar-07	433
2	24-Mar-07	567
2	25-Mar-07	356
2	26-Mar-07	356

and i want the out put following in manner

R_Date	        1	2

10-Mar-07	10	44
11-Mar-07	1	555
12-Mar-07	11	64
14-Mar-07	23	35
17-Mar-07	44	7765
18-Mar-07	23	354
19-Mar-07	54	35
20-Mar-07	24	66
21-Mar-07	66	53
22-Mar-07	34	567
23-Mar-07	25	433
24-Mar-07	33	567
25-Mar-07	67	356
26-Mar-07	35	356

how can i write the select query to get desired output.

Pankaj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-14 : 04:55:28

SELECT R_DATE, SUM(CASE WHEN R_ID = 1 THEN R_Value ELSE 0 END) AS [1],
SUM(CASE WHEN R_ID = 2 THEN R_Value ELSE 0 END) AS [2]
FROM Table1
GROUP BY R_DATE
ORDER BY R_DATE

E 12°55'05.63"
N 56°04'39.26"

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-14 : 05:31:46

quote:
Originally posted by Peso

SELECT R_DATE, SUM(CASE WHEN R_ID = 1 THEN R_Value ELSE 0 END) AS [1],
SUM(CASE WHEN R_ID = 2 THEN R_Value ELSE 0 END) AS [2]
FROM Table1
GROUP BY R_DATE
ORDER BY R_DATE

E 12°55'05.63"
N 56°04'39.26"

Hi peso,
above query gives correct output but my table having lots of R_id's so, is there any need to attach the statement for each ID ? also each R_id have the name and i want to show this R_Name instead of R_ID.

Pankaj

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-14 : 05:38:47

Yes.
SQL Server does not support dynamic pivoting.

You'll have to write or re-use an existing solution for dynamic pivoting.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-14 : 06:35:16

Try
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-14 : 07:05:31