name date xuqiu gongying
塑胶1 2010-05-17 960 1255
塑胶1 2010-05-24 964 1254
塑胶2 2010-05-17 877 5266
塑胶2 2010-05-24 778 1500SQL查询时变为如下形式:
2010-05-17 2010-05-24
塑胶1 960(需求) 964(需求)
1255(供应) 1254(供应)
塑胶2 877 778
5266 1500
SQL语句怎么写,要具体点,不要存储过程。请高人指点!!
塑胶1 2010-05-17 960 1255
塑胶1 2010-05-24 964 1254
塑胶2 2010-05-17 877 5266
塑胶2 2010-05-24 778 1500SQL查询时变为如下形式:
2010-05-17 2010-05-24
塑胶1 960(需求) 964(需求)
1255(供应) 1254(供应)
塑胶2 877 778
5266 1500
SQL语句怎么写,要具体点,不要存储过程。请高人指点!!
-- Author : htl258(Tony)
-- Date : 2010-05-15 20:56:55
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([name] [nvarchar](10),[date] [datetime],[xuqiu] [int],[gongying] [int])
INSERT INTO [tb]
SELECT '塑胶1','2010-05-17','960','1255' UNION ALL
SELECT '塑胶1','2010-05-24','964','1254' UNION ALL
SELECT '塑胶2','2010-05-17','877','5266' UNION ALL
SELECT '塑胶2','2010-05-24','778','1500'--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @S VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,date,23)) FROM tb GROUP BY [date]
EXEC('
SELECT CASE TYPE WHEN ''gongying'' THEN name ELSE '''' END name,'+@S+'
FROM (
SELECT name,convert(VARCHAR,date,23) DATE,VALUE,TYPE
FROM tb
UNPIVOT(VALUE FOR TYPE IN(xuqiu,gongying)) B
) A
PIVOT(MAX(VALUE) FOR DATE IN('+@S+')) B
ORDER BY B.name,CASE TYPE WHEN ''gongying'' THEN 1 ELSE 2 END
')
/*
name 2010-05-17 2010-05-24
---------- ----------- -----------
塑胶1 1255 1254
960 964
塑胶2 5266 1500
877 778(4 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-05-15 20:56:55
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([name] [nvarchar](10),[date] [datetime],[xuqiu] [int],[gongying] [int])
INSERT INTO [tb]
SELECT '塑胶1','2010-05-17','960','1255' UNION ALL
SELECT '塑胶1','2010-05-24','964','1254' UNION ALL
SELECT '塑胶2','2010-05-17','877','5266' UNION ALL
SELECT '塑胶2','2010-05-24','778','1500'--SELECT * FROM [tb]-->SQL查询如下:
DECLARE @S VARCHAR(8000)
SELECT @S=isnull(@S+',','')+quotename(convert(VARCHAR,date,23)) FROM tb GROUP BY [date]
EXEC('
SELECT CASE TYPE WHEN ''gongying'' THEN '''' ELSE name END name,'+@S+'
FROM (
SELECT name,convert(VARCHAR,date,23) DATE,VALUE,TYPE
FROM tb
UNPIVOT(VALUE FOR TYPE IN(xuqiu,gongying)) B
) A
PIVOT(MAX(VALUE) FOR DATE IN('+@S+')) B
ORDER BY B.name,CASE TYPE WHEN ''gongying'' THEN 1 ELSE 0 END
')
/*
name 2010-05-17 2010-05-24
---------- ----------- -----------
塑胶1 960 964
1255 1254
塑胶2 877 778
5266 1500(4 行受影响)
*/供应需求对调一下。