SELECT @@SERVERNAME
SELECT SERVERPROPERTY('ServerName')
EXEC sp_dropserver 'TEST\SQL2005'
GO
EXEC sp_addserver 'DEMO\SQL2005' , 'local'
GO
ID Name -- ------------------ 1 Deepak Kumar Goyal 2 Niladri Biswas 2 Pratik Shaw 3 Sumi Girijan 3 Suresh Beldar 3 Jeeva BabyThe challenge is that if the Id’s are exactly two in number (e.g. count of ID = 2 is exactly two ) then the names should be concatenated with 'OR'. When Ids are more than two (e.g. Id # 3 has a count of 3) , the values should be concatenated with 'AND'
ID Name -- --------------------------------------------- 1 Deepak Kumar Goyal 2 Niladri Biswas OR Pratik Shaw 3 Sumi Girijan AND Suresh Beldar AND Jeeva Baby
CREATE TABLE #PIVOT (Employee char(8),Date datetime,Kind varchar(10),Hours numeric(4,2))
INSERT INTO #PIVOT VALUES ('AAAAA','20101001','事假',3.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101002','事假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101003','事假',4.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101010','病假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101020','病假',1.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101025','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101026','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101027','公假',8.0)
INSERT INTO #PIVOT VALUES ('AAAAA','20101027','公假',NULL)
INSERT INTO #PIVOT VALUES ('BBBBB','20101001','事假',6.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101009','病假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101019','病假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101019','病假',NULL)
INSERT INTO #PIVOT VALUES ('BBBBB','20101025','陪產假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101026','陪產假',8.0)
INSERT INTO #PIVOT VALUES ('BBBBB','20101027','陪產假',8.0)
DECLARE
@pivotcol nvarchar (4000) , -- 轉置欄位變數
@selectCol nvarchar(4000) , -- PIVOT 後要把 NULL 資料轉為 0 用
@tsql1 nvarchar(4000) , -- 動態組出的 T-SQL 語法（資料 NULL 呈現）
@tsql2 nvarchar(4000) -- 動態組出的 T-SQL 語法（資料 0 呈現）
-- 把 Kind 資料塞進 @pivotcol 和 @selectcol 變數內
SELECT
@pivotcol = ISNULL(@pivotcol + ' , ' , '') + QUOTENAME(Kind) ,
@selectcol = ISNULL(@selectcol + ' , ' , '') + 'ISNULL(' + QUOTENAME(Kind) + ',0) AS ' + QUOTENAME(Kind)
FROM #PIVOT
GROUP BY Kind
-- 方法一：資料 NULL 呈現
SET @tsql1 =
'
SELECT *
FROM
(
SELECT Employee,Kind,Hours
FROM #PIVOT
) AS P
PIVOT
(
SUM(Hours) FOR Kind IN (' + @pivotcol + ')
) AS PV
'
-- 方法二：資料 0 呈現
SET @tsql2 =
'
SELECT Employee , ' + @selectcol + '
FROM
(
SELECT Employee,Kind,Hours
FROM #PIVOT
) AS P
PIVOT
(
SUM(Hours) FOR Kind IN (' + @pivotcol + ')
) AS PV
'
EXEC sp_executesql @tsql1
EXEC sp_executesql @tsql2
![[SQL] Dynamic PIVOT - 1](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_sedY4SvLApQ7qnnY9jPXNWrPdkQ7lKNRVmwhaz8KH2DX6xB7hb6RZE8Z4wo__1qbw5PmwyB0tG2WBKRiRd6la_FJDqTlD_zIvtEEMDQHu8jZFL5llgomSMcuETJ5ZyBrYo=s0-d)
ID Sentence -- ------------------------------- 1 Hello Madam! how are you Madam? 2 She peep through the window 3 This is without any Palindrome
ID Noise -- ----- 1 ? 2 !
ID Sentence PalindromeFound PalandromicWords FoundAt -- ------------------------------- --------------- ---------------- ------------- 1 Hello Madam! how are you Madam? 2 Madam Position : 2,6 2 She peep through the window 1 peep Position : 2 3 This is without any Palindrome 0 NULL NULL
SELECT P.FirstName , P.MiddleName , -- 資料內有 NULL P.LastName , E.BirthDate , -- 資料型態為 date E.VacationHours , -- 資料型態為 smallint CONCAT(P.FirstName , P.MiddleName , P.LastName , E.BirthDate , E.VacationHours) AS Concat FROM [Person].[Person] AS P JOIN [HumanResources].[Employee] AS E ON P.BusinessEntityID = E.BusinessEntityID
![[SQL] CONCAT()-1](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_vhaPa_SlqhnEJhdNcBvKGBHMbgAqGi6u5KCjmMTBx1uqqQaRt6TRsSvue8fNyFO6PTIERHrv8YmpHt2glayxiJnXe8AloXHGRRD2bbugvt2w40800BWnrY9Mjb9CCe7EW2=s0-d)
JobID Technician Grade StartTime EndTime ----- ---------- ----- ------------------- ------------------- 1 Michael 1 2010-01-01 09:08:00 2010-01-01 12:00:00 1 John 2 2010-01-01 13:00:00 2010-01-01 16:24:00 1 Steve 3 2010-01-01 08:53:00 2010-01-01 16:53:00
JobID Technician Grade StartTime EndTime ----- ---------- ----- ------------------- ------------------- 1 John 2 2010-01-01 13:00:00 2010-01-01 16:24:00 1 Michael 1 2010-01-01 09:08:00 2010-01-01 12:00:00 1 Steve 3 2010-01-01 08:53:00 2010-01-01 09:08:00 1 Steve 3 2010-01-01 12:00:00 2010-01-01 13:00:00 1 Steve 3 2010-01-01 16:24:00 2010-01-01 16:53:00
CREATE PROCEDURE uspDemo
(
@訂單時間 AS datetime ,
@顧客編號 AS int ,
@產品編號 AS int
)
AS
BEGIN
SELECT
OH.SalesOrderID ,
OH.CustomerID ,
OD.ProductID
FROM [Sales].[SalesOrderHeader] AS OH
JOIN [Sales].[SalesOrderDetail] AS OD ON OH.SalesOrderID = OD.SalesOrderID
WHERE OH.OrderDate = ISNULL(@訂單時間 , OH.OrderDate)
AND OH.CustomerID = ISNULL(@顧客編號 , OH.CustomerID) -- 動態篩選條件
AND OD.ProductID = ISNULL(@產品編號 , OD.ProductID)
END
ID NAME PRICE -- ------- --------- 1 PROD 1 100,00 2 PROD 2 220,00 3 PROD 3 15,00 4 PROD 4 70,00 5 PROD 5 150,00And the discount coupons:
ID NAME VALUE IS_PERCENT -- ----------- ------ ---------- 1 CP 1 : -15$ 15 0 2 CP 2 : -5$ 5 0 3 CP 3 : -10% 10 1 4 CP 4 : -12$ 12 0On the IS_PERCENT column, you should understand, if 0 value is considered as real money value, if 1 then it is a percent value of the price.
ID NAME PRICE DISC_PRICE TOT_DISC RATE COUPON_NAMES -- ------ -------- ----------- --------- ------- ------------------------- 1 PROD 1 100.00$ 73.00$ 27.00$ 27.00% CP 4 : -12$ + CP 1 : -15$ 2 PROD 2 220.00$ 193.00$ 27.00$ 12.27% CP 4 : -12$ + CP 1 : -15$ 3 PROD 3 15.00$ 13.50$ 1.50$ 10.00% CP 3 : -10% 4 PROD 4 70.00$ 49.50$ 20.50$ 29.28% CP 1 : -15$ + CP 3 : -10% 5 PROD 5 150.00$ 120.00$ 30.00$ 20.00% CP 3 : -10% + CP 1 : -15$
![[SQL] 隱藏 SQL Server instance-1](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_tLANq_k6uB-ZSPPcRiqu70M62CYxXEhdNVhIB4YRWgH08bzOFqW73nw3s--KGHQy2qbb0MgDxvx46FarsqnZbaECSkBYgCTK8vDgfSOibJu8UAgHaR8AbAH7MHHsdTpDs=s0-d)
![[SQL] 隱藏 SQL Server instance-2](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_v9tKtZPSvz_RHZuvC6LXVr8TDy_cOaDgfmdUduVpOpVwKnwkydTCWpJPhkoIimiyxOXyys6q9nQB9gGAOVZYtJZ6gHOMcFdUOCtx7ceHXKCn6b-Km9rVQXTpBeA7PH_ue-=s0-d)
![[SQL] 隱藏 SQL Server instance-3](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_ty5GjjWM9kokt74yR0Xf6Iu7PpyICB7YBRbxvMFch5G84Oy84DmCABYKbROPGoOY3ss6aogC0Al9Er9jSbGVP-aTl7PLDybwGDLqZGkw7ftY6oN9vckCUPskMQG7fu4Bo=s0-d)
Employee_Name Number ManagerNumber Wage ------------- ------ ------------- ------- Jacob 345 NULL 3000.00 Marco 873 NULL 3000.00 John 844 345 1880.00 James 139 844 2010.00 Ruth 111 873 1550.00 Margaret 622 345 1300.00 Mike 999 622 2010.00 Paul 611 139 1400.00
Name Total Wage Max wage amount Max wage employees ----- ---------- --------------- ------------------ Jacob 11600.00 2010.00 James,Mike Marco 4550.00 1550.00 Ruth