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
- 8102 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
Why is there a performance difference between two similar queries where one uses union and the other uses union all?
Explain about analysis services?
hi, how to link a text file and a .rpt file in my tables of sql server and to retrieve those records for further use. reply me as soon as possible.
What Is Rdbms?
Do you know clustered and non-clustered index?
What is sql server 2000 work load governor?
What are views used for?
What is the need for indexing?
What are the kinds of subquery?
How to change the system date and time from SQL Plus Terminal ?
which backup strategy you are following at ur company
What are the differences between left join and inner join in sql server?
How to achieve Paging of records in SQL SERVER?
Give some Scenario for Non Clusterd index? Can we write system defined functions in side The Function? Wat is the Unique Datatype?
How do you delete a data source?
