QUERY : Changing row to column

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)

QUERY : Changing row to column

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2008-04-21 : 22:14:21

Hi all,

I have a problem writing a query to change row to column with some conditions and computations. It's appreciated if you guys can give some ideas.

Below is the sample data:
CREATE TABLE Item (Line int, Code nvarchar(8),Price decimal, Check nvarchar(1))
INSERT INTO Item VALUES (1,'001',200 ,'Y')
INSERT INTO Item VALUES (2,'002',300 ,'Y')
INSERT INTO Item VALUES (3,'003',500 ,'Y')
INSERT INTO Item VALUES (4,'004',1000,'N')
INSERT INTO Item VALUES (5,'005',2000,'N')

The expected result :
Line with check = 'N' must be converted to column(s).

Line Code Price Item004 Item005
1 001 200 1000 * (200/200+300+500) 2000 * (200/200+300+500)
2 002 300 1000 * (300/200+300+500) 2000 * (300/200+300+500)
3 003 500 1000 * (500/200+300+500) 2000 * (500/200+300+500)

Note : I would like to avoid using any cursors if possible.

Thanks in advance.

cheers,
erwine

... sql is fun...

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-04-21 : 22:32:29

You should re-think the table structure.

Is there a reason the Check 'Y" rows are in the same table with the check 'N' rows?




declare @Item table  (Line int, Code nvarchar(8),Price decimal, [Check] nvarchar(1))
INSERT INTO @Item VALUES (1,'001',200 ,'Y')
INSERT INTO @Item VALUES (2,'002',300 ,'Y')
INSERT INTO @Item VALUES (3,'003',500 ,'Y')
INSERT INTO @Item VALUES (4,'004',1000,'N')
INSERT INTO @Item VALUES (5,'005',2000,'N')


declare @ItemColumns table  (Line int, Code nvarchar(8),Price decimal, [Check] nvarchar(1))

Insert into @ItemColumns
Select * 
From @Item a
where a.[Check] = 'N'

--Your math looks to be wrong, I assumed you wanted 200/(200+300+500) not 200/200 then add 300+500

Select a.*,b.Price,
(b.Price * (a.price/(Select Sum(aa.Price) from  @Item aa where aa.[Check] = 'Y'))) as Calculated
from @Item a
cross Join
@ItemColumns b
where  
a.[Check] = 'y'

/* RESULTS
1 001 200 Y 1000 200.000000
1 001 200 Y 2000 400.000000
2 002 300 Y 1000 300.000000
2 002 300 Y 2000 600.000000
3 003 500 Y 1000 500.000000
3 003 500 Y 2000 1000.000000

I would strongly suggest using a Reporting tool to display the results properly rather then converting sql row data into colums.
*/

erwine
Yak Posting Veteran

69 Posts

Posted - 2008-04-21 : 22:44:42

Hi Vinnie,

Thanks for replying. Unfortunately, the structure cannot be changed anymore. And you are right, the records in column 'Item004' should be (x * (200/(200+300+500)). The logic behind it is to apportion the price of item with condition Check = 'N' to each item Y(Check ='Y').
And I can only display item with Check ='Y' (3rows) in this case.

cheers,
erwine

... sql is fun...

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-04-21 : 23:00:14

look up examples of Pivot table querries. That'll get you started.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources