Left aligning a Result Table - 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)

Left aligning a Result Table

Author

Topic

tux_abzolute
Starting Member

1 Post

Posted - 2009-10-21 : 16:53:43

I have data for a Org Chart in this format:

Emp1 Sup4 Sup3 Sup2 Sup1 Null <Record1>
Emp2 Sup2 Sup1 Null Null Null <Record2>
Emp3 Sup1 Null Null Null Null <Record3>

I need...

Sup1 Sup2 Sup3 Sup4 Emp1 Null <Record1>
Sup1 Sup2 Emp2 Null Null Null <Record2>
Sup1 Emp3 Null Null Null Null <Record3>

Sup1 Is the same person throught out but Employee3 could be the same person as Sup2. In other words I need to take every person and create the supervisor chain up to the top boss.

I have this chain in the format above through a query. The problem I am having is I need to "Left Align" the data and have Sup1 be in the First column no matter how many levels are under him.

Any thoughts of suggestions would be greatly helpful.

gaauspawcscwcj
Starting Member

29 Posts

Posted - 2009-10-22 : 00:35:53

try this
--------------------
BEGIN
declare
@Emp1 varchar(50),
@Sup0 varchar(50),
@Sup1 varchar(50),
@Sup2 varchar(50),
@Sup3 varchar(50),
@Sup4 varchar(50)

declare reverse_cursor CURSOR
For
SELECT
Emp1,
Sup4,
Sup3,
Sup2,
Sup1,
Sup0
FROM tblreverse
OPEN reverse_cursor
FETCH NEXT FROM reverse_cursor
INTO
@Emp1,
@Sup4,
@Sup3,
@Sup2,
@Sup1,
@Sup0
-- loop cursor
Declare
@Null_Value varchar(300),
@Sub varchar(300)
WHILE @@FETCH_STATUS = 0
BEGIN
set @Sub = @Sup1
set @Null_Value = ''
if @Sup2 is not Null
set @Sub = @Sub + ' ' + @Sup2
else
set @Null_Value = 'NULL'

if @Sup3 is not Null
set @Sub = @Sub + ' ' + @Sup3
else
set @Null_Value = @Null_Value + ' ' + 'NULL'

if @Sup4 is not Null
set @Sub = @Sub + ' ' + @Sup4
else
set @Null_Value = @Null_Value + ' ' + 'NULL'

if @Sup0 is not Null
set @Sub = @Sub + ' ' + @Sup0
else
set @Null_Value = @Null_Value + ' ' + 'NULL'
-- insert data to temp table
insert into tblResult (result) values (@Sub + ' ' + @Emp1 + ' ' + @Null_Value )
-- next record
FETCH NEXT FROM reverse_cursor
INTO
@Emp1,
@Sup4,
@Sup3,
@Sup2,
@Sup1,
@Sup0
END
CLOSE reverse_cursor
DEALLOCATE reverse_cursor
-- get data
select * from tblResult
END
----------------------------------

gaauspawcscwcj

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-22 : 02:11:40

tux_abzolute,

your table is not normalized ?

you could use UNPIVOT and then use row_number() to generate the required sequence number for the column value for PIVOT back

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

gaauspawcscwcj
Starting Member

29 Posts

Posted - 2009-10-22 : 02:14:22

table tblResult :

CREATE TABLE [dbo].[tblResult](
[result] [varchar](300) NULL
) ON [PRIMARY]

-- table tblReverse

CREATE TABLE [dbo].[tblreverse](
[Emp1] [varchar](50) NULL,
[Sup4] [varchar](50) NULL,
[Sup3] [varchar](50) NULL,
[Sup2] [varchar](50) NULL,
[Sup1] [varchar](50) NULL,
[Sup0] [varchar](50) NULL
) ON [PRIMARY]

anything not good ???

gaauspawcscwcj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources