Hi all,
I need query help for below senorio, could you please help
me.
TableName = City
CITYID ContinuationID CITYNAME
1 1 SAN
1 2 DIEGO
2 1 SAN
2 2 FRANCISCO
3 1 CHICAGO
4 1 NEW
4 2 YORK
4 3 CITY
Could you please help me to write a generalized SQL that
returns results as given below in the
Query result
CITYID NAME1 NAME2 NAME3 NAME4 NAME5
1 SAN DIEGO
2 SAN FRANCISCO
3 CHICAGO
4 NEW YORK CITY
- 5 Answers
- 7450 Views
- TCS, I also Faced
- E-Mail Answers
Answer Posted / 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
| Is This Answer Correct ? | 8 Yes | 0 No |
Post New Answer View All Answers
What are ddl triggers and types of ddl trigger?
What are different types of constraints?
What happens if ntwdblib.dll is missing on your machine?
What is the datatype of rowid?
What is difference between foreign key and unique key?
What is a table called, if it has neither cluster nor non-cluster index? What is it used for?
How do you delete duplicate rows in sql server?
What is change tracking in sql server?
What are the different types of collation sensitivity?
Do you know what is fill factor and pad index?
How to download microsoft sql server 2005 express edition?
When does the auto update index statistics feature in sql server turn itself on?q) what specific conditions database should meet, before you can bulk copy data into it using bcp?
What are pages and extents? : SQL Server Architecture
Difference between DELETE and TRUNCATE?
What is a view and what are its advantages?
