台灣最大程式設計社群網站
線上人數
1514
 
會員總數:243452
討論主題:188175
歡迎您免費加入會員
討論區列表 >> MS SQL >> SQL組合字串的問題
[]  
[我要回覆]
1
回應主題 加入我的關注話題 檢舉此篇討論 將提問者加入個人黑名單
SQL組合字串的問題
價值 : 50 QP  點閱數:511 回應數:3

樓主

zul
門外漢
0 1
36 0
發送站內信

請問各位高手:有誰能用白話的方式告訴我下列SQL的組合字串,用途和說明!謝謝

我是在C#中找到的前人組的字串,但是我不清楚用途和說明,所以想請各位高手幫幫忙,看一下,並且說明教學一下。謝謝

"DECLARE @strColumns_01 NVARCHAR(50);" +
"DECLARE @strColumns_02 NVARCHAR(50);" +
"DECLARE @strColumns_03 NVARCHAR(50);" +
"DECLARE @strColumns_04 NVARCHAR(50);" +
"DECLARE @strColumns_05 NVARCHAR(50);" +
"DECLARE @strColumns_06 NVARCHAR(50);" +
"DECLARE @strColumns_07 NVARCHAR(50);" +
"DECLARE @strColumns_08 NVARCHAR(50);" +
"DECLARE @strColumns_09 NVARCHAR(50);" +
"DECLARE @strColumns_10 NVARCHAR(50);" +
"DECLARE @strColumns_11 NVARCHAR(50);" +
"DECLARE @strColumns_12 NVARCHAR(50);" +
"DECLARE @strColumns_13 NVARCHAR(50);" +
"DECLARE @strColumns_14 NVARCHAR(50);" +
"DECLARE @strColumns_15 NVARCHAR(50);" +
"DECLARE @strColumns_16 NVARCHAR(50);" +
"DECLARE @strColumns_17 NVARCHAR(50);" +
"DECLARE @strColumns_18 NVARCHAR(50);" +
"DECLARE @strColumns_19 NVARCHAR(50);" +
"DECLARE @strColumns_20 NVARCHAR(50);" +
"SELECT @strColumns_01=case when (ColumnValue_01 is null) or (rtrim(ColumnValue_01)='') then 'ColumnValue_01' else ColumnValue_01 end," +
"@strColumns_02=case when (ColumnValue_02 is null) or (rtrim(ColumnValue_02)='') then 'ColumnValue_02' else ColumnValue_02 end," +
"@strColumns_03=case when (ColumnValue_03 is null) or (rtrim(ColumnValue_03)='') then 'ColumnValue_03' else ColumnValue_03 end," +
"@strColumns_04=case when (ColumnValue_04 is null) or (rtrim(ColumnValue_04)='') then 'ColumnValue_04' else ColumnValue_04 end," +
"@strColumns_05=case when (ColumnValue_05 is null) or (rtrim(ColumnValue_05)='') then 'ColumnValue_05' else ColumnValue_05 end," +
"@strColumns_06=case when (ColumnValue_06 is null) or (rtrim(ColumnValue_06)='') then 'ColumnValue_06' else ColumnValue_06 end," +
"@strColumns_07=case when (ColumnValue_07 is null) or (rtrim(ColumnValue_07)='') then 'ColumnValue_07' else ColumnValue_07 end," +
"@strColumns_08=case when (ColumnValue_08 is null) or (rtrim(ColumnValue_08)='') then 'ColumnValue_08' else ColumnValue_08 end," +
"@strColumns_09=case when (ColumnValue_09 is null) or (rtrim(ColumnValue_09)='') then 'ColumnValue_09' else ColumnValue_09 end," +
"@strColumns_10=case when (ColumnValue_10 is null) or (rtrim(ColumnValue_10)='') then 'ColumnValue_10' else ColumnValue_10 end," +
"@strColumns_11=case when (ColumnValue_11 is null) or (rtrim(ColumnValue_11)='') then 'ColumnValue_11' else ColumnValue_11 end," +
"@strColumns_12=case when (ColumnValue_12 is null) or (rtrim(ColumnValue_12)='') then 'ColumnValue_12' else ColumnValue_12 end," +
"@strColumns_13=case when (ColumnValue_13 is null) or (rtrim(ColumnValue_13)='') then 'ColumnValue_13' else ColumnValue_13 end," +
"@strColumns_14=case when (ColumnValue_14 is null) or (rtrim(ColumnValue_14)='') then 'ColumnValue_14' else ColumnValue_14 end," +
"@strColumns_15=case when (ColumnValue_15 is null) or (rtrim(ColumnValue_15)='') then 'ColumnValue_15' else ColumnValue_15 end," +
"@strColumns_16=case when (ColumnValue_16 is null) or (rtrim(ColumnValue_16)='') then 'ColumnValue_16' else ColumnValue_16 end," +
"@strColumns_17=case when (ColumnValue_17 is null) or (rtrim(ColumnValue_17)='') then 'ColumnValue_17' else ColumnValue_17 end," +
"@strColumns_18=case when (ColumnValue_18 is null) or (rtrim(ColumnValue_18)='') then 'ColumnValue_18' else ColumnValue_18 end," +
"@strColumns_19=case when (ColumnValue_19 is null) or (rtrim(ColumnValue_19)='') then 'ColumnValue_19' else ColumnValue_19 end," +
"@strColumns_20=case when (ColumnValue_20 is null) or (rtrim(ColumnValue_20)='') then 'ColumnValue_20' else ColumnValue_20 end " +
"FROM TableName1 where DataDate='" + strDatadate + "';" +
"DECLARE @strSQL NVARCHAR(4000);" +
"set @strSQL = N'SELECT MAX(SerNo) as SerNo, " +
"CASE WHEN (GROUPING(DataDate) = 1) THEN ''ALL''" +
"WHEN (GROUPING(DataDate)<>1) AND (GROUPING(DeptName) = 1) THEN ''''" +
"ELSE ISNULL(DataDate, ''UNKNOWN'')" +
"END AS DataDate," +
"CASE WHEN (GROUPING(DeptName2) = 1) THEN ''ALL''" +
"WHEN (GROUPING(DeptName2)<>1) AND (GROUPING(DeptName) = 1) THEN ''''" +
"ELSE ISNULL(DeptName2, ''UNKNOWN'')" +
"END AS DeptName2," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN ''小計''" +
"ELSE ISNULL(DeptName, ''UNKNOWN'')" +
"END AS DeptName," +
"SUM(NumOfMember) as ''NumOfMember''," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_01)/SUM(NumOfMember) ELSE SUM(Value_01) END as ['+@strColumns_01+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_02)/SUM(NumOfMember) ELSE SUM(Value_02) END as ['+@strColumns_02+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_03)/SUM(NumOfMember) ELSE SUM(Value_03) END as ['+@strColumns_03+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_04)/SUM(NumOfMember) ELSE SUM(Value_04) END as ['+@strColumns_04+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_05)/SUM(NumOfMember) ELSE SUM(Value_05) END as ['+@strColumns_05+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_06)/SUM(NumOfMember) ELSE SUM(Value_06) END as ['+@strColumns_06+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_07)/SUM(NumOfMember) ELSE SUM(Value_07) END as ['+@strColumns_07+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_08)/SUM(NumOfMember) ELSE SUM(Value_08) END as ['+@strColumns_08+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_09)/SUM(NumOfMember) ELSE SUM(Value_09) END as ['+@strColumns_09+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_10)/SUM(NumOfMember) ELSE SUM(Value_10) END as ['+@strColumns_10+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_11)/SUM(NumOfMember) ELSE SUM(Value_11) END as ['+@strColumns_11+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_12)/SUM(NumOfMember) ELSE SUM(Value_12) END as ['+@strColumns_12+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_13)/SUM(NumOfMember) ELSE SUM(Value_13) END as ['+@strColumns_13+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_14)/SUM(NumOfMember) ELSE SUM(Value_14) END as ['+@strColumns_14+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_15)/SUM(NumOfMember) ELSE SUM(Value_15) END as ['+@strColumns_15+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_16)/SUM(NumOfMember) ELSE SUM(Value_16) END as ['+@strColumns_16+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_17)/SUM(NumOfMember) ELSE SUM(Value_17) END as ['+@strColumns_17+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_18)/SUM(NumOfMember) ELSE SUM(Value_18) END as ['+@strColumns_18+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_19)/SUM(NumOfMember) ELSE SUM(Value_19) END as ['+@strColumns_19+']," +
"CASE WHEN (GROUPING(DeptName) = 1) THEN SUM(NumOfMember*Value_20)/SUM(NumOfMember) ELSE SUM(Value_20) END as ['+@strColumns_20+']," +
"SUM(Value_01)+SUM(Value_02)+SUM(Value_03)+SUM(Value_04)+SUM(Value_05)+SUM(Value_06)+SUM(Value_07)+SUM(Value_08)+SUM(Value_09)+SUM(Value_10) " +
"+SUM(Value_11)+SUM(Value_12)+SUM(Value_13)+SUM(Value_14)+SUM(Value_15)+SUM(Value_16)+SUM(Value_17)+SUM(Value_18)+SUM(Value_19)+SUM(Value_20) as ''SUM''," +
"MAX(OrderSeq) as OrderSeq " +
"FROM TableName2 where DataDate=''" + strDatadate + "'' " + strSQL_Where +
"GROUP BY GROUPING SETS ((DataDate, DeptName2, DeptName),(DataDate, DeptName2))" +
"ORDER BY OrderSeq,DataDate desc';" +
"EXECUTE sp_executesql @strSQL;";


搜尋相關Tags的文章: [ C# ] , [ SQL ] , [ MSSQL ] ,
本篇文章發表於2018-01-25 17:32
別忘捐VP感謝幫助你的人 新手會員瞧一瞧
1樓
您在visual studio 中設中斷點 把 @strSQL 貼出來放在 SQL Server Management Studio
上面跑 看是啥結果 從結果推回去
本篇文章回覆於2018-01-25 17:51
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
2樓
回應

小魚
檢舉此回應
@開頭的有點像變數,
我是沒這樣用過,
通常是拿來參數查詢用的。
本篇文章回覆於2018-01-25 19:39
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
3樓
最有價值解答

真的有點難
檢舉此回應
我隨便貼一下我寫的一個procedure 他只是在C#用 純文字 把它組成一個procedure

visual studio
"EXECUTE sp_executesql @strSQL;"; 這行上設個中斷點 去看 這個procedure 看到結果就會知道 過程在做啥


ALTER procedure [dbo].[p_test]
as
create table #xx
(日期 datetime null,
花的錢 int null)

insert #xx
select '2017/01/20',100
insert #xx
select '2017/01/21',200
insert #xx
select '2017/01/22',300
insert #xx
select '2017/01/23',400
insert #xx
select '2017/01/24',500
insert #xx
select '2017/01/25',600

declare @最小一天 datetime=(select min(日期) from #xx(nolock))

create table #rep(我 nvarchar(50) null)
insert #rep
select 'kent'

declare @sql nvarchar(100)=''
declare @sql1 nvarchar(100)=''
declare @i int
dECLARE @MAX int
SET @i = 0
SET @MAX =(select datediff(day,(select min(日期) from #xx(nolock)),(select max(日期) from #xx(nolock))+1) )
WHILE (@i<@MAX)
BEGIN
set @sql='alter table #rep add ['+convert(char(10),@最小一天,111)+'] int null'
execute(@sql)

set @sql1='update #rep set ['+convert(char(10),@最小一天,111)+']=(select 花的錢 from #xx where 日期='''+convert(char(10),@最小一天,111)+''')'
execute(@sql1)
set @最小一天=@最小一天+1
Set @i=@i+1
END

select * from #rep
本篇文章回覆於2018-01-26 18:13
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
   
1

回覆
如要回應,請先登入.