November 20, 2011 at 8:52 pm
Declare @Tmp Table
(ID int,
Name varchar(100),
SUBjectName varchar(100),
Marks varchar(50))
INSERT INTO @Tmp
SELECt '1','A','math' ,'80'
UNION ALL
SELECt '1','A','English' ,'80'
UNION ALL
SELECt '1','A','Hindi' ,'90'
UNION ALL
SELECt '2','B','math' ,'100'
UNION ALL
SELECt '2','B','Marathi' ,'80'
UNION ALL
SELECt '2','B','English' ,'80'
UNION ALL
SELECt '2','B','Hindi' ,'90'
RESULT
ID Name SUBjectName Marks
1 A math 80
1 A English 80
1 A Hindi 90
2 B math 100
2 B Marathi 80
2 B English 80
2 B Hindi 90
I wan Result This Format
ID Name Sub1 Mark1 Sub2 Mark2 ..
1 a Math 80 english 80 hindi 90 Null Null
2 b Math 100 english 80 hindi 90 Marathi 80
November 20, 2011 at 11:58 pm
November 21, 2011 at 2:59 am
I tried it by Pivot Function But it work with only Aggregate Function.
My Requirement to Pivot Multiple Row with Multiple Column to Single Rows
---:-)
November 21, 2011 at 3:09 am
I don it by using Cursor..
DECLARE @HIPTMP TABLE
( [ID] [int] NULL,
[HIP1Person_Name] [varchar](50) ,
[HIP1RelationShip] [varchar](10),
[HIP1Amount] [int] NULL,
[HIP1Casetype] [varchar](40),
[HIP1Addess] [varchar](100))
DECLARE @Person_Name varchar(50)
DECLARE @RelationShip varchar(50)
DECLARE @Amount int
DECLARE @Casetype varchar(40)
DECLARE @Addess varchar(100)
DECLARE @ID INT
DECLARE HIP_CURSOR CURSOR FOR
SELECT DISTINCT ID
FROM HIPCASES
OPEN HIP_CURSOR
FETCH NEXT FROM HIP_CURSOR
INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @HIPTMP
SELECT * FROM HIPCases WHERE ID=@ID
SET @Person_Name=NULL
SET @RelationShip=null
SET @Amount=0
SET @Casetype= null
SET @Addess = NULL
SELECT @Person_Name=[HIP1Person_Name],
@RelationShip=[HIP1RelationShip],
@Amount=ISNULL([HIP1Amount],0),
@Casetype=[HIP1Casetype],
@Addess=[HIP1Addess]
FROM @HIPTMP WHERE [ID]=@ID AND [HIP1Casetype]='HIP1'
INSERT INTO [HIPCases_DETAIL]([ID],[HIP1Person_Name],[HIP1RelationShip],[HIP1Amount],[HIP1Casetype],[HIP1Addess])
VALUES(@ID,@Person_Name,@RelationShip,@Amount,@Casetype,@Addess)
UPDATE HD SET HD.[HIP2Person_Name]= HT.[HIP1Person_Name],
HD.[HIP2RelationShip]=HT.[HIP1RelationShip],
HD.[HIP2Amount]=HT.[HIP1Amount],
HD.[HIP2Casetype]=HT.[HIP1Casetype],
HD.[HIP2Addess] =HT.[HIP1Addess]
FROM [HIPCases_DETAIL] HD INNER JOIN @HIPTMP HT
ON HD.ID=HT.ID WHERE HT.[HIP1Casetype]='HIP2'
UPDATE HD SET HD.[HIP3Person_Name]= HT.[HIP1Person_Name],
HD.[HIP3RelationShip]=HT.[HIP1RelationShip],
HD.[HIP3Amount]=HT.[HIP1Amount],
HD.[HIP3Casetype]=HT.[HIP1Casetype],
HD.[HIP3Addess] =HT.[HIP1Addess]
FROM [HIPCases_DETAIL] HD INNER JOIN @HIPTMP HT
ON HD.ID=HT.ID WHERE HT.[HIP1Casetype]='HIP3'
UPDATE HD SET HD.[HIP4Person_Name]= HT.[HIP1Person_Name],
HD.[HIP4RelationShip]=HT.[HIP1RelationShip],
HD.[HIP4Amount]=HT.[HIP1Amount],
HD.[HIP4Casetype]=HT.[HIP1Casetype],
HD.[HIP4Addess] =HT.[HIP1Addess]
FROM [HIPCases_DETAIL] HD INNER JOIN @HIPTMP HT
ON HD.ID=HT.ID WHERE HT.[HIP1Casetype]='HIP4'
--SELECT * from @HIPTMP
DELETE FROM @HIPTMP
FETCH NEXT FROM HIP_CURSOR
INTO @ID
END
CLOSE HIP_CURSOR
DEALLOCATE HIP_CURSOR
🙂
November 21, 2011 at 4:04 am
Please don't use a CURSOR.
I'm not particularly good with PIVOT, mainly because I think that pivoting of data should occur in the presentation layer rather than the database. So this is probably not the best way to go about it either but should get you started.
DECLARE @Tmp TABLE (ID INT, Name VARCHAR(100), SUBjectName VARCHAR(100), Marks VARCHAR(50))
INSERT INTO @Tmp
SELECT '1','A','math' ,'80'
UNION ALL
SELECT '1','A','English' ,'80'
UNION ALL
SELECT '1','A','Hindi' ,'90'
UNION ALL
SELECT '2','B','math' ,'100'
UNION ALL
SELECT '2','B','Marathi' ,'80'
UNION ALL
SELECT '2','B','English' ,'80'
UNION ALL
SELECT '2','B','Hindi' ,'90'
SELECT a.ID, a.Name, Sub1, Mark1, Sub2, Mark2, Sub3, Mark3, Sub4, Mark4
FROM (SELECT ID, Name, MAX(Mark1) AS Mark1, MAX(Mark2) AS Mark2, MAX(Mark3) AS Mark3, MAX(Mark4) AS Mark4
FROM (SELECT ID, Name, SUBjectName, Marks,
'Mark' + CONVERT(VARCHAR(2),ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL))) AS rn
FROM @Tmp) a
PIVOT (MAX(Marks)
FOR rn IN ([Mark1],[Mark2],[Mark3],[Mark4]) ) b
GROUP BY ID, Name) a
INNER JOIN (SELECT ID, Name, MAX(Sub1) AS Sub1, MAX(Sub2) AS Sub2, MAX(Sub3) AS Sub3, MAX(Sub4) AS Sub4
FROM (SELECT ID, Name, SUBjectName, Marks,
'Sub' + CONVERT(VARCHAR(2),ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL))) AS rn
FROM @Tmp) a
PIVOT (MAX(SUBjectName)
FOR rn IN ([Sub1],[Sub2],[Sub3],[Sub4]) ) b
GROUP BY ID, Name) b ON a.ID = b.ID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply