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 measure group, measure? : sql server analysis services, ssas

0 Answers  

---

How to Execute an Operating System Command From Within SQL Server ?

3 Answers  

---

Mention the different authentication modes in sql server.

0 Answers  

---

wat is mean by normalization?......programing defination i need...(i know basic defination)

4 Answers   Wipro,

---

wht's the differece between sqlserver05 and sqlserver2000

3 Answers  

---

---

Explain the working of sql privileges?

0 Answers  

---

How do we upgrade from SQL Server 6.5 to 7.0 and 7.0 to 2000?

0 Answers  

---

The Difference between 'Count' and 'Count(*)'

13 Answers  

---

What are the 3 types of schema?

0 Answers  

---

List out the differences between the clustered index and non-clustered index in sql server?

0 Answers  

---

i want table name basis on column name.

5 Answers  

---

Their are two tables 'A' and'B'.Table 'A' contains 3 columns named 'eid','ename','dept'. Table 'B'contains 3 columns named'sid','designation','salary'. We have to retrieve the names of employees working in the same department,same designation and same salary. Its urgent can anyone help me out in this problem.

6 Answers   Tech Mahindra,

---