Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 8749 Views
- TCS, I also Faced
- E-Mail Answers
Answer Posted / 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
| Is This Answer Correct ? | 5 Yes | 0 No |
Post New Answer View All Answers
How do I manually uninstall an instance of sql server 2016?
What is difference between unique and primary key?
What is Lock table in SQL?
What is report subscription?
what is a correlated sub-query? : Sql server database administration
What is the difference between set and select?
How to enter binary string literals in ms sql server?
When would you use a before or after trigger?
What is Service Broker in sql server 2012?
Explain about Normalization?
What are policy management terms?
What is an indexing technique?
Do you know what is bit data type and whats the information that can be stored inside a bit column?
What is the recovery model?
Can we return Data from 4(more than 1) tables in stored procedure?
