โดย jinky153 » 30/06/2010 12:06 am
DECLARE @SQLCMD VARCHAR(MAX)
IF OBJECT_ID('View2') IS NOT NULL
BEGIN
DROP VIEW View2
END
SET @SQLCMD = 'CREATE VIEW View2 AS
SELECT DISTINCT Color
FROM Table1'
EXECUTE(@SQLCMD)
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast(Color as varchar) + ']',
'[' + cast(Color as varchar)+ ']'
)
FROM View2
IF OBJECT_ID('View3') IS NOT NULL
BEGIN
DROP VIEW View3
END
SET @SQLCMD = '
CREATE VIEW View3 AS SELECT Type_,'+ @PivotColumnHeaders +' FROM Table1
PIVOT (COUNT(Color)
FOR Color IN('+ @PivotColumnHeaders +'))
AS P'
EXECUTE(@SQLCMD)
DECLARE @test VARCHAR(MAX)
SELECT @test =
COALESCE(
@test + ',SUM(' + cast(Color as varchar) + ') ' + cast(Color as varchar),
'SUM(' + cast(Color as varchar)+ ') ' + cast(Color as varchar)
)
FROM View2
SET @SQLCMD = '
SELECT Type_,'+ @test +' FROM View3
GROUP BY Type_'
EXECUTE(@SQLCMD)
DECLARE @SQLCMD VARCHAR(MAX)
IF OBJECT_ID('View2') IS NOT NULL
BEGIN
DROP VIEW View2
END
SET @SQLCMD = 'CREATE VIEW View2 AS
SELECT DISTINCT Color
FROM Table1'
EXECUTE(@SQLCMD)
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast(Color as varchar) + ']',
'[' + cast(Color as varchar)+ ']'
)
FROM View2
IF OBJECT_ID('View3') IS NOT NULL
BEGIN
DROP VIEW View3
END
SET @SQLCMD = '
CREATE VIEW View3 AS SELECT Type_,'+ @PivotColumnHeaders +' FROM Table1
PIVOT (COUNT(Color)
FOR Color IN('+ @PivotColumnHeaders +'))
AS P'
EXECUTE(@SQLCMD)
DECLARE @test VARCHAR(MAX)
SELECT @test =
COALESCE(
@test + ',SUM(' + cast(Color as varchar) + ') ' + cast(Color as varchar),
'SUM(' + cast(Color as varchar)+ ') ' + cast(Color as varchar)
)
FROM View2
SET @SQLCMD = '
SELECT Type_,'+ @test +' FROM View3
GROUP BY Type_'
EXECUTE(@SQLCMD)