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  

What is the full meaning of dml?

0 Answers  

What is set nocount on?

0 Answers  

your sql server is running out of disk space. You notice that there are several large files with ldf extensions what are these files? : Sql server administration

0 Answers  

How to modify existing triggers using "alter trigger"?

0 Answers  

What are the new features in sql server 2016?

0 Answers  

What is log shipping? Can we do logshipping with SQL Server 7.0 - Logshipping is a new feature of SQL Server 2000. We should have two SQL Server - Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.

0 Answers  

What functions can a view be used to performed?

0 Answers  

Explain steps of normalization?

0 Answers  

Explain what is use of dbcc commands?

0 Answers  

How many levels of sp nesting is possible?

0 Answers  

Name the different type of indexes in sql?

0 Answers