Very Complicate Query.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Very Complicate Query....

Author

Topic

rajeshnrh74
Starting Member

5 Posts

Posted - 2011-02-12 : 10:04:07

Hi, this is my table with records

CREATE TABLE [dbo].[Person](
[RtnSetId] [int] NOT NULL,
[FirstName] [nvarchar](50) NULL,
[SSN] [nvarchar](50) NULL,
[PersonRole] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (1, N'A', N'12345', N'P ')
INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (1, N'A', N'12345', N'S ')
INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (1, N'A', N'12345', N'D ')
INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (1, N'A', N'12345', N'D ')
INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (1, N'A', N'12345', N'D ')
INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (2, N'B', N'67890', N'P ')
INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (2, N'B', N'67890', N'S ')

I want the output as :
1) It should generate dynamic columns
2) It should pick personrole in the order of 'P', 'S', 'D'
3) Record should be updated into column wise based on the group by rtnsetid

RtnSetId1 FirstName1 SSN1 PersonRole1 RtnSetId2 FirstName2 SSN2 PersonRole2 RtnSetId3 FirstName3 SSN3 PersonRole3 RtnSetId4 FirstName4 SSN4 PersonRole4 RtnSetId5 FirstName5 SSN5 PersonRole5
1 A 12345 P 1 A 12345 S 1 A 12345 D 1 A 12345 D 1 A 12345 D
2 B 67890 P 2 B 67890 S
Pls. anybody who really interested in SQL's help me out.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-12 : 16:25:50

A person really interested in sql know how to use Search...