Answer / prakash

CREATE TABLE CITY (CITYID INT, ContinuationID INT, CITYNAME
VARCHAR(50))
GO
INSERT CITY
SELECT 1 ,1, 'SAN'
UNION ALL
SELECT 1, 2, 'DIEGO'
UNION ALL
SELECT 2, 1, 'SAN'
UNION ALL
SELECT 2, 2, 'FRANCISCO'
UNION ALL
SELECT 3, 1, 'CHICAGO'
UNION ALL
SELECT 4, 1, 'NEW'
UNION ALL
SELECT 4, 2, 'YORK'
UNION ALL
SELECT 4, 3, 'CITY'


SELECT cityid,
max(CASE P.ContinuationID WHEN 1 THEN
P.CITYNAME ELSE '' END) AS Name1,
max(CASE P.ContinuationID WHEN 2 THEN
P.CITYNAME ELSE '' END) AS Name2,
max(CASE P.ContinuationID WHEN 3 THEN
P.CITYNAME ELSE '' END) AS Name3
FROM CITY AS P
GROUP BY P.cityid

Answer / gopi muluka

Using PIVOT operator we can achieve this in SQL 2005

CREATE TABLE CITY (CITYID INT, ContinuationID INT, CITYNAME
VARCHAR(50))
GO
INSERT CITY
SELECT 1 ,1, 'SAN'
UNION ALL
SELECT 1, 2, 'DIEGO'
UNION ALL
SELECT 2, 1, 'SAN'
UNION ALL
SELECT 2, 2, 'FRANCISCO'
UNION ALL
SELECT 3, 1, 'CHICAGO'
UNION ALL
SELECT 4, 1, 'NEW'
UNION ALL
SELECT 4, 2, 'YORK'
UNION ALL
SELECT 4, 3, 'CITY'
GO
SELECT CITYID, [1] AS NAME1,[2] AS NAME2,[3] AS NAME3, [4]
AS NAME4
FROM
(
SELECT CITYID,ContinuationID, CITYNAME
FROM CITY
) P
PIVOT
(
MAX(CITYNAME)
FOR ContinuationID IN
([1],[2],[3],[4])
) AS PVT
ORDER BY CITYID
GO
DROP TABLE CITY

Answer / sandeep modapathi

Hi all
Gopi Muluka is right... but here I'm posting one different
thing.. ie the out put will be like this

Only two columns

It uses a function for concatination

CityID CityName
1 SAN DIEGO
2 SAN FRANCISCO
3 CHICAGO
4 NEW YORK CITY


CREATE FUNCTION DBO.GROUP_CONCAT(@CITYID VARCHAR(100))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @STRING VARCHAR(8000)
SET @STRING=''
SELECT @STRING=@STRING+' ' +CITYNAME FROM CITY
WHERE CITYID=@CITYID ORDER BY CONTINUATIONID
RETURN LTRIM(@STRING)
END

select distinct CITYID,dbo.GROUP_CONCAT(CITYID)as CityName
from city

Answer / 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

Answer / das

select cityid,group_concat(cityname) as city from city group by cityid;

logshipping is Any difference 2000 and 2005?

2 Answers  

---

tell me what are the steps you will take to improve performance of a poor performing query? : Sql server database administration

0 Answers  

---

find the 3rd max salary

10 Answers  

---

What are the pros and cons of creating extended stored procedures?

1 Answers  

---

What is the difference between Drop and Truncate

20 Answers   GT Nexus, IBM,

---

---

Suppose i have a table that contains 5 columns like col1,col2...colm5.I want to import only two column through BCP utility.How to do same through BCP in sybase.

0 Answers   HCL,

---

How to select all columns of all rows from a table with a select statement in ms sql server?

0 Answers  

---

What does asynchronous call backs means?

0 Answers   Alcatel-Lucent,

---

is there a column to which a default can't be bound? : Sql server database administration

0 Answers  

---

What is 'write-ahead log' in sql server 2000 ?

0 Answers  

---

After the SQL installation what are the jobs will you configure?

1 Answers   G7 CR Technologies, IBM, Satyam,

---

What is an index in a database?

0 Answers  

---