Dynamic Each Column Query - 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 2008 Forums

Transact-SQL (2008)

Dynamic Each Column Query

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-04-23 : 09:29:49

Hi,
Need help in Query to built Dynamic Columns and its values in Rows. Dynamic Columns can done by Pivot, My Issue is about calculations and its variable as column and the variables value must be in rows.

TableA:
[ID] [Name] [Salary] [Incentive]
1 XXX 100 50
2 YYY 150 100
3 ZZZ 200 150

Here the Salary is fixed in table. Let we take the Salary column as Variable t.
My Calculation: t+[Incentive]
Have to take the column [Salary] (t) as 50,100,150,200 upto 500.
So its upto 10 intervals.
And need the above interval Values as Columns.

Solution:
[Name] [50] [100] [150] [200] [250] [300] [350] [400] [450] [500]
XXX 100 150 200 250 300 350 400 450 500 550
YYY 150 200 250 300 350 400 450 500 550 600
ZZZ 200 250 300 350 400 450 500 550 600 650

Regards,
Kalai

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-23 : 10:03:51

are the intervals dynamic or arbitrarily create by you.
Also if you want to get answers quicker I would encourage you to create sample data ad the code involved ie


declare @Kalaiselvan table(id int, name varchar(10), salary int, incentive int)


insert into @Kalaiselvan
VALUES(1, 'XXX', 100, 50)
insert into @Kalaiselvan
VALUES(2, 'YYY', 150, 100)
insert into @Kalaiselvan
 VALUES(3, 'ZZZ', 200, 150)

SELECT * FROM @Kalaiselvan

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-23 : 10:20:33

if you take salary as t the results you show seems to be inaccurate. 50+100 = 150

I am using the pivot function from madhivanan
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx


create table dbo.Kalaiselvan(id int, name varchar(10), salary int, incentive int)

insert into dbo.Kalaiselvan
VALUES(1, 'XXX', 100, 50)
insert into Kalaiselvan
VALUES(2, 'YYY', 150, 100)
insert into Kalaiselvan
 VALUES(3, 'ZZZ', 200, 150)

create table dbo.intervals (interval int)
insert into intervals
Select 50 
 union 
Select 100 
 union 
Select 150 
 union 
Select 200 
 union 
Select 250 
 union 
Select 300 
 union 
Select 350 
 union 
Select 400 
 union 
Select 450 
 union 
Select 500 


EXEC dynamic_pivot
'SELECT name, (salary + interval) as var
  FROM dbo.Kalaiselvan
  cross apply dbo.intervals',
'interval',
'sum(var)'




drop table Kalaiselvan
drop table intervals

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2012-04-24 : 19:35:23

You are just adding the columns [Salary] and [Incentive] and showing it in the Grid.
Yes am taking the Salary as t, But not like you mentioned as 100+50=150.
Its interval t+Incentive.
So for 1st Column [50] --> t=50 + [Incentive] = 100 and repeats.

Output will be as like I mentioned in my 1st post.

Regards,
Kalai

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources