Answer Posted / manoj

How about this, a single dynamic query:

create table cities (CITYID int, ContinuationID int,
CITYNAME varchar(50))

insert into cities
select 1, 1, 'SAN' UNION
select 1, 2, 'DIEGO' UNION
select 2, 1, 'SAN' UNION
select 2, 2, 'FRANCISCO' UNION
select 3, 1, 'CHICAGO' UNION
select 4, 1, 'NEW' UNION
select 4, 2, 'YORK' UNION
select 4, 3, 'CITY' UNION
select 5, 1, 'CITY1' UNION
select 5, 2, 'CITY2' UNION
select 5, 3, 'CITY3' UNION
select 5, 4, 'CITY4' UNION
select 5, 5, 'CITY5'

select * from cities

select distinct CITYID,
case
when exists(select ContinuationID from cities
where CITYID = c.CITYID and ContinuationID=1)
then (select CITYNAME from cities
where CITYID = c.CITYID and ContinuationID=1)
else ''
end as name1,
case
when exists(select ContinuationID from cities
where CITYID = c.CITYID and ContinuationID=2)
then (select CITYNAME from cities
where CITYID = c.CITYID and ContinuationID=2)
else ''
end as name2,
case
when exists(select ContinuationID from cities
where CITYID = c.CITYID and ContinuationID=3)
then (select CITYNAME from cities
where CITYID = c.CITYID and ContinuationID=3)
else ''
end as name3,
case
when exists(select ContinuationID from cities
where CITYID = c.CITYID and ContinuationID=4)
then (select CITYNAME from cities
where CITYID = c.CITYID and ContinuationID=4)
else ''
end as name4,
case
when exists(select ContinuationID from cities
where CITYID = c.CITYID and ContinuationID=5)
then (select CITYNAME from cities
where CITYID = c.CITYID and ContinuationID=5)
else ''
end as name5
from cities c
where ContinuationID=1

drop table cities

What is a database in ms sql server?

1037

---

What are the 3 types of schema?

1087

---

How to enter comments in transact-sql statements?

925

---

What are the acid properties?

988

---

What function does a database engine serve in the sql server?

1021

---

How to generate create table script on an existing table in ms sql server?

1132

---

What is the difference between deallocate cursor and close cursor?

1228

---

In which format does an image save in SQL Server database ?

980

---

How to write a query with a full outer join in ms sql server?

1054

---

what are the new features introduced in sql server 2000? : Sql server database administration

898

---

How important do you consider cursors or while loops for a transactional database?

947

---

What is the difference between MVC and Teir Architecher? Plz explain with Layyered Programming example...? Thanks

2043

---

Explain what are partitioned views and distributed partitioned views?

930

---

What are dml (data manipulation language) statements in ms sql server?

965

---

What are subqueries in sql server? Explain its properties.

996

---