SQL Query

[rchagoor]

Hi Guys,
I want to retrive the records as
Val col1 col2 col3
--- ---- ---- ----
1 A B C
1 X Y NULL

From The Table, Having Records shown below using select, pivot table or any other query, but not with cursor.

Thanks in Advance.


Val col1 col2 col3
----------- ----- ----- -----
1 A NULL NULL
1 NULL B NULL
1 NULL NULL C
1 X NULL NULL
1 NULL Y NULL

[dim]

Use the union to achieve the result, then you can create a view and select from the view after:

create table #tempt (val int, col1 char(1), col2 char(1), col3 char(1))

insert into #tempt
values (1, 'A', 'B', 'C')

select val, col1, null col2, null col3 from #tempt
union
select val, null col1, col2, null col3 from #tempt
union
select val, null col1, null col2, col3 from #tempt

[MAK]

Dim.

rchagoor needs the other way. the vice versa

[Geeta]

What if the data was like:

Val col1 col2 col3
----------- ----- ----- -----
1 A NULL NULL
1 NULL B NULL
1 NULL C NULL
1 NULL NULL C
1 NULL NULL D
1 NULL NULL E
1 X NULL NULL
1 NULL Y NULL

Are you trying to get all the values listed for a particular Val in the columns?

As such a cursor needs to be written picking up a Val, looking for distinct values in col1 for a Val, and similarly for col2 etc.

[dim]

Hi, MAK, I just shown a technic here. you can add the "order by ..." to make the result look exactly how rchagoor wants...

create table #tempt (val int, col1 char(1), col2 char(1), col3 char(1))

insert into #tempt
values (1, 'A', 'B', 'C')

select val, col1, null col2, null col3 from #tempt
union
select val, null col1, col2, null col3 from #tempt
union
select val, null col1, null col2, col3 from #tempt
order by 1,4,3

[shefalin]

Hi,

Try the following:
It does not use a cursor, but does use a simple while loop.
Also this would delete the source table, so please be aware of this.
You could also avoid the While loop by executing the statments within the while loop seperately.

drop table temp1
drop table temp2
create table temp1 (val int, col1 char(1), col2 char(1), col3 char(1))
create table temp2 (val int, col1 char(1), col2 char(1), col3 char(1))

insert temp1 values (1,'A',NULL,NULL)
insert temp1 values (1,NULL,'B',NULL)
insert temp1 values (1,NULL,NULL,'C')
insert temp1 values (1,'X',NULL,NULL)
insert temp1 values (1,NULL,'Y',NULL)

select * from temp1

delete from temp2 -- destination
WHILE 1 = 1
BEGIN

if (select count(*) from temp1) = 0
break

insert into temp2
select distinct a.val,
(select top 1 col1 from temp1 b where a.val = b.val and b.col1 is not null ) as col1 ,
(select top 1 col2 from temp1 c where a.val = c.val and c.col2 is not null ) as col2,
(select top 1 col3 from temp1 d where a.val = d.val and d.col3 is not null ) as col3
from temp1 a

set rowcount 3
-- delete from temp1 -- uncomment this delete if ur sure
set rowcount 0

End
select * from temp2

[rchagoor]

Thanks Shefali.