合并具有相同ID的两行并创建新列 [英] merge two rows having same id and create a new column
本文介绍了合并具有相同ID的两行并创建新列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的阙是
i我在下面的数据表中获取数据
id date hours
1 22/7/13 4
1 23/7/13 5
10 22/7/13 4
10 23/7/13 3
11 24 / 7/13 8
6 25/7/13 8
6 26/7/13 8
i want以下表格中的数据
id 22/7/13 23/7/13 24/7/13 25/7/13 26/7/13
1 4 5 - - -
10 4 3 - - -
11 - - 8 - -
6 - - - 8 8
有人知道怎么做...请帮助
解决方案
是的,可以借助 PIVOT [ ^ ]。
在SQL查询中使用Pivot的简单方法 [ ^ ]
希望这会有所帮助。
试试这个:
SET DATEFORMAT dmy;
CREATE TABLE #tmp(id INT ,日期 DATETIME ,小时 INT )
INSERT INTO #tmp(id,< span class =code-keyword> date ,hours)
SELECT 1 ,' 22/07/2013', 4
UNION ALL SELECT 1 ,' 23/07/2013', 5
UNION ALL SELECT 10 ,' 22/07/2013 ', 4
UNION ALL SELECT 10 ,' 23/07/2013', 3
UNION ALL SELECT 11 ,' 24/07/2013', 8
UNION ALL SELECT 6 ,' 25/07/2013', 8
UNION ALL SELECT 6 ,' 26/07/2013', 8
DECLARE @cols VARCHAR ( 300 )
DECLARE @ dt VARCHAR ( 2000 )
< span class =code-keyword> DECLARE @ pt VARCHAR ( 4000 )
SET @cols = STUFF(( SELECT DISTINCT ' ],[' + CONVERT ( VARCHAR ( 10 ),O。 date , 121 )
< span class =code-keyword> FROM #tmp AS O
ORDER BY ' ],[' + CONVERT ( VARCHAR ( 10 ),O 。 date , 121 )
FOR XML PATH(' ')), 1 , 2 ,' ' )+ ' ]'
SET @ dt = N ' SELECT id,CONVERT(VARCHAR(10),date,121)AS date,hours
FROM #tmp'
EXEC ( @ dt )
SET @ pt = N ' SELECT id,' + @cols + ' ' +
' FROM(' + @ dt + ' )AS DT' +
' PIVOT(MAX(小时)FOR日期IN(' + @cols + ' ))AS PT' +
' ORDER BY id'
EXEC ( @ pt )
DROP 表 #tmp
my que is
i am getting data in datatable in below form
id date hours
1 22/7/13 4
1 23/7/13 5
10 22/7/13 4
10 23/7/13 3
11 24/7/13 8
6 25/7/13 8
6 26/7/13 8
i want this data in below form
id 22/7/13 23/7/13 24/7/13 25/7/13 26/7/13
1 4 5 - - -
10 4 3 - - -
11 - - 8 - -
6 - - - 8 8
can anyone know how to do this...pls help
解决方案
Yes its possible with help of PIVOT[^].
Simple Way To Use Pivot In SQL Query[^]
Hope this helps.
Try this:
SET DATEFORMAT dmy; CREATE TABLE #tmp (id INT, date DATETIME, hours INT) INSERT INTO #tmp (id, date, hours) SELECT 1, '22/07/2013', 4 UNION ALL SELECT 1, '23/07/2013', 5 UNION ALL SELECT 10, '22/07/2013', 4 UNION ALL SELECT 10, '23/07/2013', 3 UNION ALL SELECT 11, '24/07/2013', 8 UNION ALL SELECT 6, '25/07/2013', 8 UNION ALL SELECT 6, '26/07/2013', 8 DECLARE @cols VARCHAR(300) DECLARE @dt VARCHAR(2000) DECLARE @pt VARCHAR(4000) SET @cols = STUFF((SELECT DISTINCT '],[' + CONVERT(VARCHAR(10),O.date,121) FROM #tmp AS O ORDER BY '],[' + CONVERT(VARCHAR(10),O.date,121) FOR XML PATH('')),1,2,'') + ']' SET @dt = N'SELECT id, CONVERT(VARCHAR(10),date,121) AS date, hours FROM #tmp' EXEC(@dt) SET @pt = N'SELECT id, ' + @cols + ' ' + 'FROM(' + @dt + ') AS DT ' + 'PIVOT(MAX(hours) FOR date IN(' + @cols + ')) AS PT ' + 'ORDER BY id' EXEC(@pt) DROP TABLE #tmp
这篇关于合并具有相同ID的两行并创建新列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文