how to use a column value into next 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)

how to use a column value into next column

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-04-02 : 02:30:39

Hi all,

Below is my table with three columns:

Parent Child Incharge
------------------------
A100 X414 KDL
A100 U541 KDL
A100 O554 KDL
B200 K641 KDL
B200 S774 KDL
C300 Y455 KDL
D400 Z475 KDL

I would like to show the above table as below:

Parent Child Incharge
------------------------
Parent A100 -
A100 X414 KDL
A100 U541 KDL
A100 O554 KDL
Parent B200 -
B200 K641 KDL
B200 S774 KDL
Parent C300 -
C300 Y455 KDL
Parent D400 -
D400 Z475 KDL

Kindly assist me how to do this. Thanks.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-02 : 03:03:38

declare @tbl table(Parent varchar(20),Child varchar(20),incharge varchar(30))
insert into @tbl
select 'A100', 'X414','KDL' union all
select 'A100', 'U541','KDL' union all
select 'A100', 'O554', 'KDL' union all
select 'B200', 'K641','KDL' union all
select 'B200', 'S774','KDL' union all
select 'C300', 'Y455', 'KDL' union all
select 'D400', 'Z475', 'KDL'

select case when child is null and incharge is null then 'Parent' + ' ' + Parent else Parent end,child,incharge from
(
select Parent,child,incharge from @tbl
group by Parent,Child,incharge
with rollup
having GROUPING(Parent)^GROUPING(Child)^GROUPING(incharge)=0

)t
order by Parent,child

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources