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
- 8100 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
What is side by side migration in sql server?
How to add more data to the testing table in ms sql server?
What is the use of group by clause?
What are the different types of replication you can set up in sql server?
What is trigger and different types of Triggers?
You have modified 100 store procedures and want to replicate these changes from development to prodution, and production can have users using the Server/DB, how would you replicate without causing issues?
What is the meaning of lock escalation and why/how to stop this? : sql server database administration
Do you think BCNF is better than 2NF & 3NF? Why?
Does group by sort data?
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.
Define ACID properties in a Database?
Explain what is public role in sql server?
Why do we use stored procedures in sql server?
Which is the best place or learning center for MS SQL?????In Bangladesh?????
Explain optimistic and pessimistic concurrency?
