How to write Pivot Query Multiplte Table Joins and my Questions and answers Table.

Posted by Jayakumars under ASP.NET AJAX on 10/22/2012 | Points: 10 | Views : 1118 | Status : [Member] [MVP] | Replies : 6
hi

How to write this output For Pivot Table.

Execute this Query First -> Text File.




I need output this image check below

Mark as Answer if its helpful to you


Responses

Posted by: Seng2hs on: 10/23/2012 [Member] Starter | Points: 25

Up
0
Down
Please explain what is your expectation. so that i can try to solve ur problem


Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 10/23/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
check my attachment file.

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Seng2hs on: 10/23/2012 [Member] Starter | Points: 25

Up
0
Down
---------------------------
WinZip
---------------------------
Cannot open file: it does not appear to be a valid archive.

If you downloaded this file, try downloading the file again.
---------------------------
OK Help
---------------------------

Upload the attachment again

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 10/23/2012 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
hello i have download and use this extracing correctly what u say? download winzip s/w and extract it
if u know solve them.

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gaur1982 on: 10/25/2012 [Member] Starter | Points: 25

Up
0
Down
I didn't understand you problem exactly but i will give a code of Dynamic Pivoting..

CREATE TABLE [dbo].[UserDetail](
[PK_User] [bigint] NOT NULL,
[FK_Property] [int] NOT NULL,
[PropValue] [varchar](100) NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (1, 1, N'Jitendra')
INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (1, 2, N'Garg')
INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (2, 1, N'Praveen')
INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (2, 2, N'Mehta')
INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (2, 3, N'Udaipur')

GO

ALTER PROCEDURE [dbo].[usp_Pivot]
(
@UserId As INT
)
As
DECLARE @Query As VARCHAR(MAX)

DECLARE @StrColumn As VARCHAR(MAX) = ''
SELECT @StrColumn = CASE @StrColumn WHEN '' THEN '' ELSE @StrColumn + ',' END+ 'ISNULL(['+ CAST(FK_Property AS VARCHAR(10)) +'],'''') AS Col' + CAST(FK_Property AS VARCHAR(10)) FROM dbo.UserDetail(NoLock) WHERE PK_User = @UserId

DECLARE @StrIn As VARCHAR(MAX) = ''
SELECT @StrIn = CASE @StrIn WHEN '' THEN '' ELSE @StrIn + ',' END+ '['+ CAST(FK_Property AS VARCHAR(10)) +']' FROM dbo.UserDetail(NoLock) WHERE PK_User = @UserId

SET @Query =
'SELECT * FROM
(
SELECT
PK_User As UserId,
'+ @StrColumn +'
FROM
(
SELECT
PK_User,FK_Property,PropValue
FROM dbo.UserDetail(NoLock) WHERE PK_User = ' + CAST(@UserId AS VARCHAR(10)) + '
) p
PIVOT
(
MAX (PropValue)
FOR FK_Property IN ('+ @StrIn +')
) AS pvt
)X'

PRINT @Query
EXEC (@Query)
GO

I hope this code will helps a lot to you for making your concept by using your logic....

Gaurav Agrawal
http://www.planetofcoders.com/

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gaur1982 on: 10/25/2012 [Member] Starter | Points: 25

Up
0
Down
when i opening your attached file it gives me an error and this error screenshot is attached with this message....

I think you are using a worst zip convertor/maker software. I suggest you to use by default windows OS zip functionality because it is the best.....
 Download source file

Gaurav Agrawal
http://www.planetofcoders.com/

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response