Pivoting - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Pivoting

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2009-05-22 : 02:56:54

Hi there

I have 3 tables:

1) Services:
ServiceID INT,
ServiceNo VARCHAR(32)

2) ChartAccounts:
ChartAccountID INT,
ChartAccountCode VARCHAR(32)

3) ServiceChartAccounts:
ServiceID INT,
ChartAccountID INT,
SplitPercentage DECIMAL(5,2)

So if I joinining these 3 tables I might end up with for instance:

ServiceID, ServiceNo, SplitPercentge, ChartAccountID, ChartAccountCode
1, 0296620382, 100, 1000, 'IT-HELPDESK-7000'
2, 0399999999, 50, 1000, 'IT-HELPDESK-7000'
2, 0399999999, 50, 1001, 'IT-SYSTEM-7000'

As you can see the 0399999999 has 2 splits 50 - 50 between 'IT-HELPDESK-7000' and 'IT-SYSTEM-7000'

I want to display in my grids as follow:

ServiceNo| SplitPercentage of ChartAccountCode
0296620382| 100% - IT-HELPDESK-7000
0399999999| 50% - IT-HELPDESK-7000, 50% - IT-SYSTEM-7000

Is it possible to do this (pivoting) without using the cursor?

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 03:13:03

yup..its possible. do like below


SELECT s.ServiceID,
STUFF((SELECT ','+ CAST(sca.SplitPercentage AS varchar(5)) + ca.ChartAccountCode 
      FROM ChartAccounts ca
      INNER JOIN ServiceChartAccounts sca
      ON sca.ChartAccountID=ca.ChartAccountID
      WHERE ServiceID=s.ServiceID
      FOR XML PATH('')),1,1,'')
FROM Services s

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-05-22 : 03:13:18

if any can group it together i also wanna learn ^^

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 03:21:45

quote:
Originally posted by waterduck

if any can group it together i also wanna learn ^^

group together what?

dewacorp.alliances

452 Posts

Posted - 2009-05-22 : 07:00:03

quote:


SELECT s.ServiceID,
STUFF((SELECT ','+ CAST(sca.SplitPercentage AS varchar(5)) + ca.ChartAccountCode 
      FROM ChartAccounts ca
      INNER JOIN ServiceChartAccounts sca
      ON sca.ChartAccountID=ca.ChartAccountID
      WHERE ServiceID=s.ServiceID
      FOR XML PATH('')),1,1,'')
FROM Services s

Hi Visakh16

Is this new in SQL05? Never heard about this STUFF and XML Path thingy?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-22 : 07:06:21

quote:

Originally posted by dewacorp.alliances

quote:


SELECT s.ServiceID,
STUFF((SELECT ','+ CAST(sca.SplitPercentage AS varchar(5)) + ca.ChartAccountCode 
      FROM ChartAccounts ca
      INNER JOIN ServiceChartAccounts sca
      ON sca.ChartAccountID=ca.ChartAccountID
      WHERE ServiceID=s.ServiceID
      FOR XML PATH('')),1,1,'')
FROM Services s

Hi Visakh16

Is this new in SQL05? Never heard about this STUFF and XML Path thingy?

Yes. It is

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources