Show the row wise data into column wise

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)

Show the row wise data into column wise

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

Posted - 2008-07-24 : 06:40:32

You need a dynamic pivot table for this, and hence you must use dynamic sql.
See http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

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

,
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources