Author |
Topic |
ganny
Yak Posting Veteran
51 Posts |
Posted - 2008-07-24 : 06:24:42
|
Hi,
I just facing an issue that want to display the row records into column wise.
Table: Name Month Amount ------ ------ ------- ABC Jun-07 100 XYZ Feb-07 150 AAA Mar-07 200 ABC Feb-08 200 XYZ Mar-08 100 . . . . . . . . . . . . N N N
but have to display the records as below: * i want to display only from feb-07 to mar-07 records
Output:
Name Jun-07 Feb-07 Mar-07 ------ ------ ------- ------- ABC 100 - - XYZ - 150 - AAA - - 200 . . . . . . . . . . . . N N N N
Kindly help me anyone to solve the above problem..
Thank you. |
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-07-24 : 06:27:58
|
have you looked at PIVOT in books online?
Em |
 |
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2008-07-24 : 06:28:35
|
Do forum search for "PIVOT" or "CROSSTAB" and read about that in BooksOnLine |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-24 : 06:32:45
|
[code]DECLARE @Table TABLE (Name VARCHAR(5), Month VARCHAR(7), Amount INT)
INSERT @Table SELECT 'ABC', 'Jun-07', 100 UNION ALL SELECT 'XYZ', 'Feb-07', 150 UNION ALL SELECT 'AAA', 'Mar-07', 200 UNION ALL SELECT 'ABC', 'Feb-08', 200 UNION ALL SELECT 'XYZ', 'Mar-08', 100
-- SQL Server 2000 SELECT Name, SUM(CASE WHEN Month = 'Jun-07' THEN Amount ELSE 0 END) AS [Jun-07], SUM(CASE WHEN Month = 'Feb-07' THEN Amount ELSE 0 END) AS [Feb-07], SUM(CASE WHEN Month = 'Mar-07' THEN Amount ELSE 0 END) AS [Mar-07] FROM @Table GROUP BY Name ORDER BY Name
-- SQL Server 2005 SELECT p.Name, p.[Jun-07], p.[Feb-07], p.[Mar-07] FROM @Table AS t PIVOT ( SUM(Amount) FOR Month IN ([Jun-07], [Feb-07], [Mar-07]) ) AS p[/code]
E 12°55'05.25" N 56°04'39.16" |
 |
|
ganny
Yak Posting Veteran
51 Posts |
Posted - 2008-07-24 : 06:38:53
|
Many thanks for your kind reply.. have an lit idea on PIVOT. i think, have to mention column name which we are going to display columns using PIVOT whereas my situation is slitly different..
display the records from [selected month] to [selected month].. so the output would show the all the selected months (between).
Example: i have given months from JANUARY-07 to APRIL-07, the columns should show all months from JANUARY TO APRIL.
Kindly advise me how to proceed. |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-24 : 06:41:14
|
And your requirements seems doomed.
We can't you ALWAYS return JAN-DEC, and only use the columns you want in the client application?
E 12°55'05.25" N 56°04'39.16" |
 |
|
ganny
Yak Posting Veteran
51 Posts |
Posted - 2008-07-24 : 06:51:39
|
Thanks for your response.
but, in my application.. user will enter the month and year range (Jan-07 to Apr-07) based on the input we need to show the months as columns.
please advise me whether is it not possible? |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-24 : 06:57:07
|
It is possible to do with dyanmic sql.
BUT A BETTER APPROACH is to always return 12 month columns from the database and only use the ones you need at the client application. You can also rename the columns easier at the client application.
E 12°55'05.25" N 56°04'39.16" |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-07-24 : 07:03:53
|
[code]DECLARE @Table TABLE (Name VARCHAR(5), Month VARCHAR(7), Amount INT)
INSERT @Table SELECT 'ABC', 'Jun-07', 100 UNION ALL SELECT 'XYZ', 'Feb-07', 150 UNION ALL SELECT 'AAA', 'Mar-07', 200 UNION ALL SELECT 'ABC', 'Feb-08', 200 UNION ALL SELECT 'XYZ', 'Mar-08', 100
DECLARE @FromMonth VARCHAR(12), @ToMonth VARCHAR(12)
SELECT @FromMonth = 'Mar-07', @ToMonth = 'Feb-08'
SELECT p.Name, p.[0], p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p. , p.[9], p.[10], p.[11] FROM ( SELECT Name, DATEDIFF(MONTH, CAST('1-' + @FromMonth AS DATETIME), CAST('1-' + Month AS DATETIME)) AS Delta, Amount FROM @Table WHERE CAST('1-' + Month AS DATETIME) >= CAST('1-' + @FromMonth AS DATETIME) AND CAST('1-' + Month AS DATETIME) <= CAST('1-' + @ToMonth AS DATETIME) ) AS t PIVOT ( SUM(Amount) FOR Delta IN ([0], [1], [2], [3], [4], [5], [6], [7], , [9], [10], [11]) ) AS p[/code]Now load all columns in your datagrid
1) Replace header text on all columns 2) Set those columns you want to visible = true 3) Set those columns you don't want to visible = false
E 12°55'05.25" N 56°04'39.16" |
 |
|
ganny
Yak Posting Veteran
51 Posts |
Posted - 2008-07-24 : 07:28:14
|
Thanks a lot for your kind assistance. i will implement the above in my application and try to make it possible.
once again thank you so much.:) |
 |
|
VGuyz
Posting Yak Master
121 Posts |
Posted - 2008-07-24 : 07:34:58
|
chk this..
DECLARE @MONTHS NVARCHAR(MAX) SELECT @MONTHS = '' SELECT @MONTHS = @MONTHS + '[' + month + '],' FROM (SELECT DISTINCT month FROM sales) S
SELECT @MONTHS = SUBSTRING(@MONTHS,1,LEN(@MONTHS) - 1
DECLARE @SQLSTR NVARCHAR(MAX) SELECT @SQLSTR = 'SELECT PIVOTCOLUMNS.* FROM sales S PIVOT (SUM(amount) FOR month IN (' + @MONTHS + ')) AS PIVOTCOLUMNS ORDER BY PIVOTCOLUMNS.name'
exec sp_executesql @SQLSTR |
 |
|
ganny
Yak Posting Veteran
51 Posts |
Posted - 2008-07-24 : 10:03:48
|
Thank you. |
 |
|
|