Coloumns to rows - 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

Coloumns to rows

Author

Topic

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-29 : 02:52:56

Hello All,
While implementing my task the new problem come in front
what i want to do is, suppose a table name is t1 which has two columns id and day.

id--day--Salary
A1--0--1000
A2--2--2000
B---1--3000
A3--3--4000
B---0--5000
A4--4--6000
C---6--7000
C1--8--9000
D---7--8000

I want to write a query which should give output-:
A1,A2,A3,A4--9--1000,2000,4000,6000
B---1--3000
C,C1--14--16000
D--7--8000

1st coloumn contanins that A1,A2,A3,A4 they all contains A as common so i should take it one, 2nd coloums counts the sum, and 3rd columns contains the salary

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-29 : 03:10:11

There are 2 lines of B. what happen to the line with 5000 ?

why is the Salary sum up for "C,C1--14--16000" while not for the others ?

KH
[spoiler]Time is always against us[/spoiler]

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-29 : 04:02:50

Hello All,
While implementing my task the new problem come in front
what i want to do is, suppose a table name is t1 which has two columns id and day.

id--day--Salary
A1--0--1000
A2--2--2000
B---1--3000
A3--3--4000
B1--0--5000
A4--4--6000
C---6--7000
C1--8--9000
D---7--8000

I want to write a query which should give output-:
A1,A2,A3,A4--9--1000,2000,4000,6000
B,B1---1--3000,5000
C,C1--14--7000,9000
D--7--8000

Sorry for the mistake
1st coloumn contanins that A1,A2,A3,A4 they all contains A as common so i should take it one, 2nd coloums counts the sum, and 3rd columns contains the salary

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-29 : 05:07:22

[code]
select ID = stuff (
(select ',' + x.id
from t1 x
where x.id like left(t.id, 1) + '%'
group by x.id
for xml path ('')),
1, 1, ''
),
sum(day),
Salary = stuff (
(select ',' + convert(varchar(10), salary)
from t1 x
where x.id like left(t.id, 1) + '%'
for xml path ('')),
1, 1, ''
)
from t1 t
group by left(id, 1)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2011-12-29 : 06:01:39

hats off to you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources