SQL Server Performance

Very Complicate Query....

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by rajeshnrh74, Feb 12, 2011.

  1. rajeshnrh74 New Member

    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
    RtnSetId1FirstName1SSN1PersonRole1RtnSetId2FirstName2SSN2PersonRole2RtnSetId3FirstName3SSN3PersonRole3RtnSetId4FirstName4SSN4PersonRole4RtnSetId5FirstName5SSN5PersonRole5
    1A12345P 1A12345S 1A12345D 1A12345D 1A12345D
    2B67890P 2B67890S
    Pls. anybody who really interested in SQL's help me out.
  2. Adriaan New Member

    Such a shame that you have a perfectly normalized table, and someone wants the information shown in a rather clumsy way (one row per group, and varying numbers of columns to show the individual members).
    To be honest, this is much better handled by the client application than by the back-end database - not the least because it goes against the nature of a relational database.
    Then again, if it's a programming exercise, you'd better not be asking us. [;)]

Share This Page