I have two tables employee and workinghours.
Employee:
ID Name
001 Jack
002 Peter
workinghours:
ID Day Hours
001 08/02 6
001 08/03 7
001 08/04 5
002 08/02
002 08/03 4
002 08/04 6
how to get a result like
ID Name 08/02 08/03 08/04
001 Jack 6 7 5
002 Peter 0 4 6
You can try to use JOIN with condition aggregate function to SUM with CASE WHEN do pivot
CREATE TABLE Employee(
ID VARCHAR(50),
Name VARCHAR(50)
);
INSERT INTO Employee values ('001','Jack');
INSERT INTO Employee values ('002','Peter');
CREATE TABLE workinghours (
ID VARCHAR(50),
Day VARCHAR(50),
Hours INT
);
INSERT INTO workinghours values ('001','08/02',6);
INSERT INTO workinghours values ('001','08/03',7);
INSERT INTO workinghours values ('001','08/04',5);
INSERT INTO workinghours values ('002','08/02',null);
INSERT INTO workinghours values ('002','08/03',4);
INSERT INTO workinghours values ('002','08/04',6);
Query 1:
SELECT t1.id,t1.name,
SUM(CASE WHEN Day='08/02' then Hours else 0 end) '08/02',
SUM(CASE WHEN Day='08/03' then Hours else 0 end) '08/03',
SUM(CASE WHEN Day='08/04' then Hours else 0 end) '08/04'
FROM
Employee t1 inner join workinghours t2 on t1.id = t2.id
GROUP BY t1.id,t1.name
| id | name | 08/02 | 08/03 | 08/04 |
|-----|-------|-------|-------|-------|
| 001 | Jack | 6 | 7 | 5 |
| 002 | Peter | 0 | 4 | 6 |
If you want to create columns dynamically, you can try to use dynamic pivot.
create the SQL syntax then use execute function dynamic execute it.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((
SELECT distinct ', SUM(CASE WHEN Day = '''+ Day + ''' then Hours else 0 end)' + ' as '''+Day+''''
FROM workinghours
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT t1.id,
t1.name, ' + @cols + '
FROM
Employee t1 inner join workinghours t2 on t1.id = t2.id
GROUP BY t1.id,t1.name '
execute(@query)
You should use PIVOT function.
Static Pivot:
You can pivot columns statically
SELECT * FROM
(SELECT e.id, e.name, day, COALESCE(Hours,0) as Hours
FROM Employee e INNER JOIN workinghours w ON e.ID=w.ID) AS t
PIVOT (sum(Hours) FOR day IN ([08/02],[08/03],[08/04])) AS p;
Result:
| id | name | 08/02 | 08/03 | 08/04 |
|-----|-------|-------|-------|-------|
| 001 | Jack | 6 | 7 | 5 |
| 002 | Peter | 0 | 4 | 6 |
Dynamic Pivot:
Maybe you don't want to create static columns table. According this post, you can also create a Dynamic Pivot table this way:
DECLARE @cols AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
SELECT @cols = @cols + QUOTENAME(Day) + ',' FROM
(SELECT DISTINCT Day FROM workinghours ) as wkh
SELECT @cols = SUBSTRING(@cols, 0, len(@cols)) --remove last ','
SET @query = 'SELECT name, ' + @cols + ' from
(SELECT e.id, e.name, day, COALESCE(Hours,0) as Hours FROM
Employee e INNER JOIN workinghours w ON e.ID=w.ID) AS x
PIVOT (sum(Hours) FOR day in (' + @cols + ')) AS p'
execute(@query);
Result:
| id | name | 08/02 | 08/03 | 08/04 |
|-----|-------|-------|-------|-------|
| 001 | Jack | 6 | 7 | 5 |
| 002 | Peter | 0 | 4 | 6 |
Important: In order to avoid SQL Injection, use QUOTENAME function when creating dynamic columns. Read more here.
Try with pivot:
with pcte as (SELECT t1.id,t1.name,day,hours
FROM
Employee t1 inner join workinghours t2 on t1.id = t2.id
)
select * from pcte
pivot(avg(hours) for day in ([08/02],[08/03],[08/04])) as pv
If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements.
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
You can find further details here or refer to a similar question here.
Refer to this link, you need a View like this:
SELECT Tbl_Employee.ID,
Tbl_Employee.Name,
Tbl_WorkingHours.Days,
Tbl_WorkingHours.Hours
FROM Tbl_Employee INNER JOIN Tbl_WorkingHours ON Tbl_Employee.ID = Tbl_WorkingHours.ID
and then use this query:
SELECT ID,
MAX(CASE WHEN (Days = '08/02') THEN Hours ELSE 0 END) AS '08/02',
MAX(CASE WHEN (Days = '08/03') THEN Hours ELSE 0 END) AS '08/03',
MAX(CASE WHEN (Days = '08/04') THEN Hours ELSE 0 END) AS '08/04'
FROM dbo.View_1
GROUP BY ID
ORDER BY ID
