Changing table into another format

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

General SQL Server Forums

New to SQL Server Programming

Changing table into another format

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2013-06-10 : 07:37:31

EmpID Date in out Hrwkd Late
1 01/06/2012 9:15 10:15 1:00 15
1 02/06/2012 9:16 10:16 1:00 16
1 03/06/2012 9:17 10:17 1:00 17
2 01/06/2012 9:15 10:15 1:00 15
2 02/06/2012 9:16 10:16 1:00 16
2 03/06/2012 9:17 10:17 1:00 17

01/06/2012 02/06/2013 03/06/2013
EmpID 1 1 1
In 9:15 9:16 9:17
Out 10:15 10:15 10:15
Late 15 16 17
EmpID 2 2 2
In 9:15 9:16 9:17
Out 10:15 10:15 10:15
Late 15 16 17

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-10 : 07:41:31

You need to use UNPIVOT for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-10 : 13:39:58

[CODE]

DECLARE @Temp1 TABLE (EmpID INT, Date1 DATE, in1 time, out1 time, Hrwkd time, Late int)
INSERT INTO @Temp1 VALUES
(1, '02/06/2012', '9:16', '10:16', '1:00', 16),
(1, '03/06/2012', '9:17', '10:17', '1:00', 17),
(2, '01/06/2012', '9:15', '10:15', '1:00', 15),
(2, '02/06/2012', '9:16', '10:16', '1:00', 16),
(2, '03/06/2012', '9:17', '10:17', '1:00', 17);

SELECT [EMP ID], [01/06/2012], [02/06/2012], [03/06/2012] FROM
(
SELECT 1 as Ord, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [Grp#],
CAST('EMP ID' AS VARCHAR(12)) as [EMP ID],
CAST(EmpID as VARCHAR(12)) as [01/06/2012],
CAST(EmpID as VARCHAR(12)) as [02/06/2012],
CAST(EmpID as VARCHAR(12)) as [03/06/2012]
FROM @Temp1 GROUP BY EmpID
UNION
SELECT 2 as Ord, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [Grp#],
CAST('In ' AS VARCHAR(12)) as [EMP ID],
MAX(CASE WHEN Date1 = '01/06/2012' THEN CAST(In1 as VARCHAR(12)) END) AS [01/06/2012],
MAX(CASE WHEN Date1 = '02/06/2012' THEN CAST(In1 as VARCHAR(12)) END) as [02/06/2012],
MAX(CASE WHEN Date1 = '03/06/2012' THEN CAST(In1 as VARCHAR(12)) END) as [03/06/2012]
FROM @Temp1 GROUP BY EmpID
UNION
SELECT 3 as Ord, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [Grp#],
CAST('Out ' AS VARCHAR(12)) as [EMP ID],
MAX(CASE WHEN Date1 = '01/06/2012' THEN CAST(Out1 as VARCHAR(12)) END) AS [01/06/2012],
MAX(CASE WHEN Date1 = '02/06/2012' THEN CAST(Out1 as VARCHAR(12)) END) as [02/06/2012],
MAX(CASE WHEN Date1 = '03/06/2012' THEN CAST(Out1 as VARCHAR(12)) END) as [03/06/2012]
FROM @Temp1 GROUP BY EmpID
UNION
SELECT 4 as Ord, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [Grp#],
CAST('Hrwkd ' AS VARCHAR(12)) as [EMP ID],
MAX(CASE WHEN Date1 = '01/06/2012' THEN CAST(Hrwkd as VARCHAR(12)) END) AS [01/06/2012],
MAX(CASE WHEN Date1 = '02/06/2012' THEN CAST(Hrwkd as VARCHAR(12)) END) as [02/06/2012],
MAX(CASE WHEN Date1 = '03/06/2012' THEN CAST(Hrwkd as VARCHAR(12)) END) as [03/06/2012]
FROM @Temp1 GROUP BY EmpID
UNION
SELECT 5 as Ord, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS [Grp#],
CAST('Late ' AS VARCHAR(12)) as [EMP ID],
MAX(CASE WHEN Date1 = '01/06/2012' THEN CAST(Late as VARCHAR(12)) END) AS [01/06/2012],
MAX(CASE WHEN Date1 = '02/06/2012' THEN CAST(Late as VARCHAR(12)) END) as [02/06/2012],
MAX(CASE WHEN Date1 = '03/06/2012' THEN CAST(Late as VARCHAR(12)) END) as [03/06/2012]
FROM @Temp1 GROUP BY EmpID
) T
ORDER BY [Grp#], Ord

[/CODE]

pnasz
Posting Yak Master

101 Posts

Posted - 2013-06-11 : 02:56:20

what if date is dynamic.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 03:06:53

quote:
Originally posted by pnasz

what if date is dynamic.

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 03:16:07

try this too


DECLARE @DateList varchar(1000)
SELECT @DateList = STUFF((SELECT DISTINCT ',[' + CONVERT(varchar(11),[Date],103) + ']' FROM Table ORDER BY ',[' + CONVERT(varchar(11),[Date],103) + ']' FOR XML PATH('')),1,1,'')

SET @SQL='SELECT Col,' + @DateList + 'FROM
(
SELECT *
FROM (SELECT DENSE_RANK() OVER (ORDER BY EmpID) AS Rnk,[EmpID],[Date],[in],[out],[Late] FROM Table)t
UNPIVOT (Val FOR Col IN ([EmpID],[in],[out],[Late]))u
)r
PIVOT (MAX(Val) FOR [Date] IN (' + @DateList + '))p'

--PRINT(@SQL)
EXEC(@SQL)

This is untested as I dont have a SQL box here
try it out and in case of error uncomment PRINT (@SQL) statement, run and post the result here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-11 : 04:46:40

I have fixed some of syntactical error with the above code...

DECLARE @DateList varchar(1000), @SQL VARCHAR(MAX) = ''
SELECT @DateList = STUFF((SELECT DISTINCT ',[' + CONVERT(varchar(11),[Date],103) + ']' FROM #Temp1 ORDER BY ',[' + CONVERT(varchar(11),[Date],103) + ']' FOR XML PATH('')),1,1,'')

SET @SQL='SELECT Col,' + @DateList + 'FROM
(
SELECT *
FROM (SELECT DENSE_RANK() OVER (ORDER BY EmpID) AS Rnk,CAST( [EmpID] AS VARCHAR(15)) [EmpID], [Date], CAST([in]  AS VARCHAR(15)) [In], CAST([out]  AS VARCHAR(15)) [out], CAST([Late]  AS VARCHAR(15)) Late  FROM #Temp1)t
UNPIVOT (Val FOR Col IN ([EmpID],[in],[out],[Late]))u
)r
PIVOT (MAX(Val) FOR [Date] IN (' + @DateList + '))p
ORDER BY Rnk'

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources