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
- 9121 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 to enable/disable indexes?
How to execute the cursor queries with "open" statements?
Why use update_statistics command in sql server?
What happens if the update subquery returns multiple rows in ms sql server?
What is transaction server consistency?
Explain what is use of dbcc commands?
Explain the concept of view and Types of views in SQL server?
What is a unique key constraint?
Explain transaction server isolation?
Create and insert into temp table in sql server?
How can you insert values in multiple rows using one Insert statement?
Why do we partition data?
What is replace and stuff function in sql server?
While using a cursor, how can you differentiate between a deleted row and a row that has been inserted with null data values?
Why do we use functions?
