CREATE TABLE TA([桥梁名称] VARCHAR(1), [详细信息] NVARCHAR(2))
INSERT TA
SELECT 'a', N'省略' UNION ALL
SELECT 'b', N'省略' UNION ALL
SELECT 'c', N'省略' UNION ALL
SELECT 'd', N'省略' UNION ALL
SELECT 'e', N'省略' UNION ALL
SELECT 'f', N'省略' UNION ALL
SELECT 'g', N'省略'CREATE TABLE TB([桥梁名称] VARCHAR(1), [检查日期] DATETIME)
INSERT TB
SELECT 'c', '2009-1-1' UNION ALL
SELECT 'b', '2009-1-2' UNION ALL
SELECT 'a', '2009-1-3' UNION ALL
SELECT 'g', '2009-1-5' UNION ALL
SELECT 'b', '2009-1-3' UNION ALL
SELECT 'c', '2009-1-4'DECLARE @STR NVARCHAR(4000)
SET @STR=N''
SELECT @STR=@STR+N','+N'MAX(CASE WHEN [检查日期]='''+CONVERT(VARCHAR(10),[检查日期],120)+N''' THEN N''√'' ELSE '''' END) AS ['+CONVERT(VARCHAR(10),[检查日期],120)+']'
FROM TB GROUP BY [检查日期]EXEC(N'SELECT TA.[桥梁名称]'+@STR+N' FROM TA LEFT JOIN TB ON TA.[桥梁名称]=TB.[桥梁名称] GROUP BY TA.[桥梁名称]')
DROP TABLE TA,TB合计半天也没搞懂 SELECT @STR=@STR+N','+N'MAX(CASE WHEN [检查日期]='''+CONVERT(VARCHAR(10),[检查日期],120)+N''' THEN N''√'' ELSE '''' END) AS ['+CONVERT(VARCHAR(10),[检查日期],120)+']'
FROM TB GROUP BY [检查日期]啥意思 哪位给解释解释原理 怎么个查发
INSERT TA
SELECT 'a', N'省略' UNION ALL
SELECT 'b', N'省略' UNION ALL
SELECT 'c', N'省略' UNION ALL
SELECT 'd', N'省略' UNION ALL
SELECT 'e', N'省略' UNION ALL
SELECT 'f', N'省略' UNION ALL
SELECT 'g', N'省略'CREATE TABLE TB([桥梁名称] VARCHAR(1), [检查日期] DATETIME)
INSERT TB
SELECT 'c', '2009-1-1' UNION ALL
SELECT 'b', '2009-1-2' UNION ALL
SELECT 'a', '2009-1-3' UNION ALL
SELECT 'g', '2009-1-5' UNION ALL
SELECT 'b', '2009-1-3' UNION ALL
SELECT 'c', '2009-1-4'DECLARE @STR NVARCHAR(4000)
SET @STR=N''
SELECT @STR=@STR+N','+N'MAX(CASE WHEN [检查日期]='''+CONVERT(VARCHAR(10),[检查日期],120)+N''' THEN N''√'' ELSE '''' END) AS ['+CONVERT(VARCHAR(10),[检查日期],120)+']'
FROM TB GROUP BY [检查日期]EXEC(N'SELECT TA.[桥梁名称]'+@STR+N' FROM TA LEFT JOIN TB ON TA.[桥梁名称]=TB.[桥梁名称] GROUP BY TA.[桥梁名称]')
DROP TABLE TA,TB合计半天也没搞懂 SELECT @STR=@STR+N','+N'MAX(CASE WHEN [检查日期]='''+CONVERT(VARCHAR(10),[检查日期],120)+N''' THEN N''√'' ELSE '''' END) AS ['+CONVERT(VARCHAR(10),[检查日期],120)+']'
FROM TB GROUP BY [检查日期]啥意思 哪位给解释解释原理 怎么个查发
FROM TB GROUP BY [检查日期]PRINT @STR
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
SET @STR=N''
SELECT @STR=@STR+N','+N'MAX(CASE WHEN [检查日期]='''+CONVERT(VARCHAR(10),[检查日期],120)+N''' THEN N''√'' ELSE '''' END) AS ['+CONVERT(VARCHAR(10),[检查日期],120)+']'
FROM TB GROUP BY [检查日期]PRINT @STR
INSERT TA
SELECT 'a', N'省略' UNION ALL
SELECT 'b', N'省略' UNION ALL
SELECT 'c', N'省略' UNION ALL
SELECT 'd', N'省略' UNION ALL
SELECT 'e', N'省略' UNION ALL
SELECT 'f', N'省略' UNION ALL
SELECT 'g', N'省略'CREATE TABLE TB([桥梁名称] VARCHAR(1), [检查日期] DATETIME)
INSERT TB
SELECT 'c', '2009-1-1' UNION ALL
SELECT 'b', '2009-1-2' UNION ALL
SELECT 'a', '2009-1-3' UNION ALL
SELECT 'g', '2009-1-5' UNION ALL
SELECT 'b', '2009-1-3' UNION ALL
SELECT 'c', '2009-1-4'DECLARE @STR NVARCHAR(4000)
SET @STR=N''
SELECT @STR=@STR+N','+N'MAX(CASE WHEN [检查日期]='''+CONVERT(VARCHAR(10),[检查日期],120)+N''' THEN N''√'' ELSE '''' END) AS ['+CONVERT(VARCHAR(10),[检查日期],120)+']'
FROM TB GROUP BY [检查日期]
PRINT N'SELECT TA.[桥梁名称]'+@STR+N' FROM TA LEFT JOIN TB ON TA.[桥梁名称]=TB.[桥梁名称] GROUP BY TA.[桥梁名称]'
--EXEC(N'SELECT TA.[桥梁名称]'+@STR+N' FROM TA LEFT JOIN TB ON TA.[桥梁名称]=TB.[桥梁名称] GROUP BY TA.[桥梁名称]')
DROP TABLE TA,TB