Data in Column wise - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Data in Column wise

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-18 : 02:22:01

My Table(collection) Structure consist of below:
Type Date Item Amount
REVENUE 2007-04-01 serv002 ***
REVENUE 2007-04-01 Serv004 ***
REVENUE 2007-04-01 serv005 ***
REVENUE 2007-04-01 AMC -***
ROYALTY 2007-04-01 serv002 ***
ROYALTY 2007-04-01 Serv004 ***
ROYALTY 2007-04-01 serv005 ***
ROYALTY 2007-04-01 AMC ***

O/p should come as:
====================
Revenue:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****

Royalty:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 02:42:45

[code]SELECT Date,
SUM(CASE WHEN Item='AMC' THEN Amount ELSE 0 END) AS AMC,
SUM(CASE WHEN Item='serv002' THEN Amount ELSE 0 END) AS serv002,
SUM(CASE WHEN Item='serv004' THEN Amount ELSE 0 END) AS serv004,
SUM(CASE WHEN Item='serv005' THEN Amount ELSE 0 END) AS serv005
FROM YourTable
WHERE Type='REVENUE'
GROUP BY Date
[/code]
similary for ROYALTY change Type='ROYALTY' in where condition.

The formatting can be done easily at your front end. What is the front end you are using?

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-18 : 03:07:17

hi visakh16,
Txs for your reply,item is not fixed.it may vary .

quote:
Originally posted by sent_sara

My Table(collection) Structure consist of below:
Type Date Item Amount
REVENUE 2007-04-01 serv002 ***
REVENUE 2007-04-01 Serv004 ***
REVENUE 2007-04-01 serv005 ***
REVENUE 2007-04-01 AMC -***
ROYALTY 2007-04-01 serv002 ***
ROYALTY 2007-04-01 Serv004 ***
ROYALTY 2007-04-01 serv005 ***
ROYALTY 2007-04-01 AMC ***

O/p should come as:
====================
Revenue:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****

Royalty:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 03:10:24

Are you using SQL 2000 or SQL 2005?

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-04-18 : 03:13:30

sql2005

quote:
Originally posted by visakh16

Are you using SQL 2000 or SQL 2005?

ranganath
Posting Yak Master

209 Posts

Posted - 2008-04-18 : 03:16:25

Try with this

Create Table #T (Type varchar(100), Date datetime, Item varchar(100),Amount int)
Insert Into #T
Select 'Revenue' , '2007-04-01' ,'serv002', 100 Union All
Select 'Revenue' , '2007-04-01' ,'Serv004', 100 Union All
Select 'Revenue' , '2007-04-01' ,'serv005', 100 Union All
Select 'Revenue' , '2007-04-01' ,'AMC', 100 Union All
Select 'Royalty' , '2007-04-01' ,'serv002', 100 Union All
Select 'Royalty' , '2007-04-01' ,'Serv004', 100 Union All
Select 'Royalty' , '2007-04-01' ,'serv005', 100 Union All
Select 'Royalty' , '2007-04-01' ,'AMC',100
--Select * From #T

Declare @Sql varchar(Max), @Str Varchar(Max)
Set @Sql = ''
Set @Str = ''
Select @Str = @Str + ', Min(Case when Item = ''' + Item +''' Then Amount End) As "'+ Item + '"'
From (Select Distinct Item From #T)A
--Select @Str

Select @Sql = 'Select Type, Date '+ @str+', Amount From #T Group By Date, Amount, Type '
Exec (@sql)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 03:24:22

Use PIVOT option:-
DECLARE @ItemList varchar(3000),@Sql varchar(8000)

SELECT @ItemList =LEFT(il.ItemList,LEN(il.ItemList)-1)
FROM (SELECT Item+ ',' AS [text()]
YouTable
WHERE Type='REVENUE'
FOR XML PATH(''))il(ItemList)

SELECT @Sql='SELECT m.Date,FROM
(SELECT Date,
Item,Amount
FROM YourTable
WHERE Type='REVENUE')m
PIVOT (SUM(Amount) FOR Item IN (['+ REPLACE(@ItemList,',','],[')+']))p'

EXEC(@Sql)

EDIT:included declaration of sql string variable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources