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
- 8958 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 #table in sql server?
What do you understand by recursive stored procedures?
What is rs.exe utility?
You want to implement the one-to-many relationship while designing tables. How would you do it?
How to link tables in sql server?
Equi join and non equi join is possible with sql server?
How extra digits are handled with numeric data type literals?
What is a cursor, index in sql?
How do you test your database? : sql server database administration
1. Tell me your daily activities 2. If sql server installation fails at time installation what will do 3. Where does the sql server installation log details are stored 4. After the installation what will you do for memory configuration 5. What is the difference between SQL max maximum memory and AWE memory 6. How will you configure AWE memory 7. How will setup an email alert for the backup job 8. After the SQL installation what are the jobs will you configure 9. What does –g mean in the sql startup parameter 10. What is the difference between Bulked log and Full recovery model 11. What is the difference between mirroring and log shipping 12. What are the steps to be followed before in-place up gradation 13. After installing the patch the sql server does not start and application team tells to rollback the changes .In this scenario what will you do
Explain triggers?
Can multiple columns be used in sql group by clause in ms sql server?
What do you understand by triggers?
What does this statement do @@rowcount?
What is the difference between a clustered index and a non-clustered index?
