Very Complicated Query...

Last Post 14 Feb 2011 04:14 AM by Tariq. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
rajeshnrh1974
New Member
New Member

--
12 Feb 2011 03:11 PM

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 give me a hand on this.

Thanks

Tariq
New Member
New Member

--
14 Feb 2011 04:14 AM
Try below code...


 
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 ') 

-- Code Start From Here
create table #temp (rtnsetid int, val varchar(5000))
Go 

insert into #temp
Select [RtnSetId],convert(varchar,[RtnSetId]) + isnull([FirstName],'') + isnull([SSN],'') + isnull([PersonRole],'')
from person

declare @val varchar(max)
        ,@Query varchar(max)

set @val=''
Select @val = Case when @val = '' then '['+val+']' else @val+ ',' + '['+val+']' end 
From (Select Distinct Val From #temp) as t


Set @query = 'Select RtnSetID,*
             From
                ( Select * from #Temp ) as Source
             PIVOT (max(val) for val in (' + @val + ')) as pvt'
Exec (@Query)


You are not authorized to post a reply.

Acceptable Use Policy