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;

What is the cpu pressure?

0 Answers  

Explain about protocol layer present in SQL server?

0 Answers  

What is normalization and what are the advantages of it?

0 Answers  

Hello all, I have data like :- year amt 2004 10 2005 20 2006 30 Now i want output as:- 2004 2005 2006 10 30 60 but i have to use here group by on year.So, i need a single query within that i can find.

3 Answers  

What is normalization of database?

0 Answers  

i use few third party softwares. they r all having their own databases . but the data is repeated in all these databases - say a person is in all the three databases, but his name is stoared in diff format in all databases i want to create a centralised database ,and i dont want to re-enter the records . using the exisating records how can i build a centralised database?

1 Answers   Fidelity,

What all db objects can be found in MSDB database of a SQL Server instance?

2 Answers   Accenture,

How to create a view using data from another view?

0 Answers  

What happens if time-only values are provided as date and time literals?

0 Answers  

What is better - 2nd Normal form or 3rd normal form? Why?

2 Answers   TCS, TPK,

What is a primary index?

0 Answers  

How to create function without parameter in sql server?

0 Answers