TSQL-動態取得TABLE值，再將值轉為欄名(運用CASE or Pivot)

2010-06-22

3360
0
T-SQL
2010-06-29

摘要:TSQL-動態取得TABLE值，再將值轉為欄名

一、產生測試資料表、資料：

--(1)、SERVICE_ITEM_INFO
CREATE TABLE [dbo].[SERVICE_ITEM_INFO]
(
    [SERVICE_ITEM_ID] [varchar](3) NOT NULL,
    [SERVICE_TYPE_ID] [varchar](2) NOT NULL,
    [SERVICE_ITEM_NAME] [varchar](50) NOT NULL,
    [SERVICE_ITEM_PRIV] [varchar](50) NULL,
    [TRANS_TYPE] [varchar](8) NULL,
CONSTRAINT [PK_SERVICE_ITEM_INFO] PRIMARY KEY CLUSTERED
(
    [SERVICE_ITEM_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Insert into SERVICE_ITEM_INFO
   (SERVICE_ITEM_ID, SERVICE_TYPE_ID, SERVICE_ITEM_NAME, SERVICE_ITEM_PRIV, TRANS_TYPE)
Values
   ('01', '01', '密碼', 'R101,R201,R301', 'SS001');
Insert into SERVICE_ITEM_INFO
   (SERVICE_ITEM_ID, SERVICE_TYPE_ID, SERVICE_ITEM_NAME, SERVICE_ITEM_PRIV, TRANS_TYPE)
Values
   ('02', '01', '國別', 'R101,R201,R301,C101,C201,C301', 'SS001');
Insert into SERVICE_ITEM_INFO
   (SERVICE_ITEM_ID, SERVICE_TYPE_ID, SERVICE_ITEM_NAME, SERVICE_ITEM_PRIV, TRANS_TYPE)
Values
   ('03', '01', '姓名-公司名稱', 'R101,R201,R301', 'SS001');
Insert into SERVICE_ITEM_INFO
   (SERVICE_ITEM_ID, SERVICE_TYPE_ID, SERVICE_ITEM_NAME, SERVICE_ITEM_PRIV, TRANS_TYPE)
Values
   ('04', '01', '證照號碼', 'R101,R201,R301', 'SS001');
Insert into SERVICE_ITEM_INFO
   (SERVICE_ITEM_ID, SERVICE_TYPE_ID, SERVICE_ITEM_NAME, SERVICE_ITEM_PRIV, TRANS_TYPE)
Values
   ('05', '01', '生日', 'R101,R201,R301', 'SS001');
GO

--(2)、V_RETAIL_SALESORG_AREA
CREATE TABLE [dbo].[V_RETAIL_SALESORG_AREA]
(
    [SALESCD] [varchar](7) NOT NULL,
    [STORENAME] [varchar](30) NOT NULL,
    [Regionname] [varchar](30) NOT NULL
) ON [PRIMARY]
GO
Insert into V_RETAIL_SALESORG_AREA
   (SALESCD, Regionname, STORENAME)
Values
   ('2102','北一區','天母門市(2104)');
Insert into V_RETAIL_SALESORG_AREA
   (SALESCD, Regionname, STORENAME)
Values
   ('2103','北二區','永和門市(2103)');
Insert into V_RETAIL_SALESORG_AREA
   (SALESCD, Regionname, STORENAME)
Values
   ('2101','北二區','台北(遠企)門市(2101)');
GO
--(3)、TRANSACTION_LOG
CREATE TABLE [dbo].[TRANSACTION_LOG]
(
    [TRANS_SN] [int] NOT NULL,
    [SALESCD] [varchar](7) NULL,
    [SERVICE_ITEM_ID] [varchar](3) NOT NULL,
    [SERVICE_DATE] [datetime] NULL,
CONSTRAINT [PK_TRANSACTION_LOG] PRIMARY KEY CLUSTERED
(
    [TRANS_SN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Insert into TRANSACTION_LOG
   (TRANS_SN, SALESCD, SERVICE_ITEM_ID, SERVICE_DATE)
Values
   (7019425, '2101', '02', '10/07/2009 08:55:45');
Insert into TRANSACTION_LOG
   (TRANS_SN, SALESCD, SERVICE_ITEM_ID, SERVICE_DATE)
Values
   (7019433, '2103', '03', '10/07/2009 10:09:47');
Insert into TRANSACTION_LOG
   (TRANS_SN, SALESCD, SERVICE_ITEM_ID, SERVICE_DATE)
Values
   (7019453, '2101', '02', '10/07/2009 16:41:15');
Insert into TRANSACTION_LOG
   (TRANS_SN, SALESCD, SERVICE_ITEM_ID, SERVICE_DATE)
Values
   (7019483, '2103', '04', '10/07/2009 19:34:53');
Insert into TRANSACTION_LOG
   (TRANS_SN, SALESCD, SERVICE_ITEM_ID, SERVICE_DATE)
Values
   (7019484, '2101', '03', '10/07/2009 20:29:00');
Insert into TRANSACTION_LOG
   (TRANS_SN, SALESCD, SERVICE_ITEM_ID, SERVICE_DATE)
Values
   (7019485, '2101', '02', '10/07/2009 20:32:43');
GO

二、資料：

select * From dbo.SERVICE_ITEM_INFO
select * From dbo.V_RETAIL_SALESORG_AREA
select * From dbo.TRANSACTION_LOG
--關聯
SERVICE_ITEM_INFO.SERVICE_ITEM_ID = TRANSACTION_LOG.SERVICE_ITEM_ID
V_RETAIL_SALESORG_AREA.SALESCD = TRANSACTION_LOG.SALESCD

三、依據上述資料，產出下列結果：

四、T-SQL：

(1)、運用CASE：
Declare @strSI_ID varchar(max),
        @strSI_Name varchar(max),
        @strSI_ID1 varchar(max),
        @strSI_ID2 varchar(max),
        @strSI_ID3 varchar(max),
        @strSI_ID4 varchar(max)
Declare @i INT
SET @strSI_ID = ''
SET @strSI_Name = ''
SET @strSI_ID1 = ''
SET @strSI_ID2 = ''
SET @strSI_ID3 = ''
SET @strSI_ID4 = ''
SET @i = (Select MAX(ROW)
                      From (
                            SELECT SERVICE_ITEM_ID, SERVICE_ITEM_NAME,
                                (ROW_NUMBER() OVER (ORDER BY SERVICE_ITEM_ID)) AS ROW
                              FROM SERVICE_ITEM_INFO
                            ) A
                    )
    WHILE @i > 0
       BEGIN
            SET @strSI_ID = (Select SERVICE_ITEM_ID
                        From (
                                SELECT SERVICE_ITEM_ID, SERVICE_ITEM_NAME,
                                    (ROW_NUMBER() OVER (ORDER BY SERVICE_ITEM_ID)) AS ROW
                                  FROM SERVICE_ITEM_INFO
                              ) A
                        Where ROW = @i
                      )
            SET @strSI_Name = (Select SERVICE_ITEM_NAME
                        From (
                                SELECT SERVICE_ITEM_ID, SERVICE_ITEM_NAME,
                                    (ROW_NUMBER() OVER (ORDER BY SERVICE_ITEM_ID)) AS ROW
                                  FROM SERVICE_ITEM_INFO
                              ) A
                        Where ROW = @i
                      )
            SET @i = @i -1
            SET @strSI_ID3 = 'SUM(ISNULL(AD.c' + @strSI_ID + 'name,0)) AS [' + @strSI_Name + '], '
            SET @strSI_ID4 = @strSI_ID4 + @strSI_ID3
            SET @strSI_ID = 'Case When a.SERVICE_ITEM_ID = ''' + @strSI_ID + ''' Then D.CNT END AS c' + @strSI_ID + 'name, '
            SET @strSI_ID1 = @strSI_ID1 + @strSI_ID
       END
            SET @strSI_ID2 = 'Select ' + @strSI_ID4 + ' AD.STORENAME AS [門市名稱], AD.Regionname AS [區域別] ' +
                             ' FROM (
                                         Select ' + @strSI_ID1 + ' D.Regionname, D.STORENAME
                                           From dbo.SERVICE_ITEM_INFO a
                                                Join
                                                (Select B.SALESCD, B.STORENAME, B.Regionname, C.SERVICE_ITEM_ID, COUNT(*) as CNT
                                                   From dbo.V_RETAIL_SALESORG_AREA B Join dbo.TRANSACTION_LOG C
                                                     ON B.SALESCD = C.SALESCD
                                                  Group by B.SALESCD, B.STORENAME, B.Regionname, C.SERVICE_ITEM_ID) D
                                            ON a.SERVICE_ITEM_ID = D.SERVICE_ITEM_ID
                                        ) AD
                               GROUP BY AD.STORENAME, AD.Regionname, AD.STORENAME '
            EXEC (@strSI_ID2)

(2)、運用Pivot：

                         Declare @strSI_ID varchar(max),
                          @strSI_NameA varchar(max),
                          @strSI_NameB varchar(max),
                          @strD1 varchar(1),
                          @strD2 varchar(max),
                          @strSI_ID21 varchar(max),
                          @strSI_ID22 varchar(max),
                          @strSI_ID23 varchar(max)

                        Declare @i INT

                        SET @strSI_ID = ''
                        SET @strSI_NameA = ''
                        SET @strSI_NameB = ''
                        SET @strD1 = ', '
                        SET @strD2 = ''
                        SET @strSI_ID21 = ''
                        SET @strSI_ID22 = ''
                        SET @strSI_ID23 = ''

                        SET @i = (Select MAX(ROW)
                             From (
                             SELECT SERVICE_ITEM_ID, SERVICE_ITEM_NAME,
                              (ROW_NUMBER() OVER (ORDER BY SERVICE_ITEM_ID)) AS ROW
                               FROM SERVICE_ITEM_INFO
                             ) A
                           )

WHILE @i > 0
BEGIN

                           SET @strSI_NameA = (Select SERVICE_ITEM_NAME
                              From (
                                SELECT SERVICE_ITEM_ID, SERVICE_ITEM_NAME,
                                 (ROW_NUMBER() OVER (ORDER BY SERVICE_ITEM_ID)) AS ROW
                                  FROM SERVICE_ITEM_INFO
                                 ) A
                              Where ROW = @i
                               )

SET @i = @i -1

                           IF @i <> 0
                           Begin
                            set @strSI_NameB = @strSI_NameB + ' [' + @strSI_NameA + ']' + @strD1
                           End
                           Else
                           Begin
                            set @strSI_NameB = @strSI_NameB + ' [' + @strSI_NameA + ']'
                           End

END

                        set @strSI_ID21 = ' Select *
                               From
                              (
                               Select storename AS [門市名稱], storename AS StoreNameCount, regionname AS [區域別], service_item_name
                                 From dbo.SERVICE_ITEM_INFO
                                 LEFT JOIN dbo.TRANSACTION_LOG
                                ON SERVICE_ITEM_INFO.SERVICE_ITEM_ID = TRANSACTION_LOG.SERVICE_ITEM_ID '
                        set @strSI_ID23 = '       LEFT JOIN dbo.V_RETAIL_SALESORG_AREA
                                ON V_RETAIL_SALESORG_AREA.SALESCD = TRANSACTION_LOG.SALESCD
                              ) as t
                              pivot ( count(StoreNameCount) for service_item_name in ( ' + @strSI_NameB + ')) as pt
                              WHERE [門市名稱] is not null'

set @strD2 = @strSI_ID21 + @strSI_ID23

EXEC (@strD2)

回首頁