如下圖：
查詢一些表，用下同的陳述句，
select CONVERT(varchar(100),t0.FPlanCommitDate,23) as day,case when t0.fheadselfj0188='缸R 1' then t1.fname else '' end as 'R1'
,case when t0.fheadselfj0188='缸R 5' then t1.fname +'('+convert(varchar(20),convert(decimal(18,0),t0.fqty,0)) + ')' else '' end as 'R5'
,case when t0.fheadselfj0188='缸R 4' then t1.fname +'('+convert(varchar(20),convert(decimal(18,0),t0.fqty,0)) + ')' else '' end as 'R4'
,case when t0.fheadselfj0188='缸R 2' then t1.fname+'('+convert(varchar(20),convert(decimal(18,0),t0.fqty,0)) + ')' else '' end as 'R2'
,case when t0.fheadselfj0188='缸R 3' then t1.fname +'('+convert(varchar(20),convert(decimal(18,0),t0.fqty,0)) + ')' else '' end as 'R3'
,case when t0.fheadselfj0188='缸R 6' then t1.fname +'('+convert(varchar(20),convert(decimal(18,0),t0.fqty,0)) + ')' else '' end as 'R6'
,case when t0.fheadselfj0188='缸R 7' then t1.fname +'('+convert(varchar(20),convert(decimal(18,0),t0.fqty,0)) + ')' else '' end as 'R7'
,case when t0.fheadselfj0188='缸R 8' then t1.fname +'('+convert(varchar(20),convert(decimal(18,0),t0.fqty,0)) + ')' else '' end as 'R8'
--,t0.FQty
from icmo t0 inner join t_ICItem t1 on t0.fitemid=t1.fitemid
--from icmo t0 ,t_ICItem t1
where
--t0.fitemid=t1.fitemid and
t0.FPlanCommitDate>=GETDATE()-3 -- and t0.fitemid<>''
order by FPlanCommitDate, FHeadSelfJ0188
常規查詢出來是這樣，中間多了好多空的：
但是我想查出來弄成這樣子，按日期從第一行排下去：
想了幾天不知道有什么方法，請各位支持一下，感謝
uj5u.com熱心網友回復：
外面在套一層，加一個where條件，把為空串的都過濾掉uj5u.com熱心網友回復：
--建表
CREATE TABLE #icmo
(
FPlanCommitDate DATE,
fheadselfj0188 VARCHAR(100),
fitemid INT,
fqty INT
)
CREATE TABLE #t_ICItem
(
fitemid INT,
fname VARCHAR(100)
)
--測驗資料
INSERT INTO #icmo VALUES('2020-10-30','缸R 1',1,10)
INSERT INTO #icmo VALUES('2020-10-30','缸R 3',2,1170)
INSERT INTO #icmo VALUES('2020-10-30','缸R 6',3,280)
INSERT INTO #icmo VALUES('2020-10-30','缸R 7',4,2000)
INSERT INTO #icmo VALUES('2020-10-30','缸R 7',4,1000)
INSERT INTO #icmo VALUES('2020-10-30','缸R 7',5,2700)
INSERT INTO #icmo VALUES('2020-10-30','缸R 8',6,8000)
INSERT INTO #icmo VALUES('2020-10-31','缸R 1',7,10)
INSERT INTO #icmo VALUES('2020-10-31','缸R 1',8,20)
INSERT INTO #icmo VALUES('2020-10-31','缸R 1',8,30)
INSERT INTO #icmo VALUES('2020-10-31','缸R 2',9,1050)
INSERT INTO #t_ICItem VALUES(1,'VA010')
INSERT INTO #t_ICItem VALUES(2,'Pre-Mix1072')
INSERT INTO #t_ICItem VALUES(3,'TT106')
INSERT INTO #t_ICItem VALUES(4,'WT152')
INSERT INTO #t_ICItem VALUES(5,'WS571F')
INSERT INTO #t_ICItem VALUES(6,'WT131W')
INSERT INTO #t_ICItem VALUES(7,'WT111')
INSERT INTO #t_ICItem VALUES(8,'Teach_sum')
INSERT INTO #t_ICItem VALUES(9,'TR132')
--查詢(先按FPlanCommitDate,fheadselfj0188分組產生一個序號,然后行列轉換的時候按FPlanCommitDate+序號聚合,搞定)
SELECT
CONVERT(varchar(100),FPlanCommitDate,23) as day
,MAX(CASE WHEN fheadselfj0188='缸R 1' then fname else '' END) as 'R1'
,MAX(CASE WHEN fheadselfj0188='缸R 5' then fname +'('+convert(varchar(20),convert(decimal(18,0),fqty,0)) + ')' else '' END) as 'R5'
,MAX(CASE WHEN fheadselfj0188='缸R 4' then fname +'('+convert(varchar(20),convert(decimal(18,0),fqty,0)) + ')' else '' END) as 'R4'
,MAX(CASE WHEN fheadselfj0188='缸R 2' then fname +'('+convert(varchar(20),convert(decimal(18,0),fqty,0)) + ')' else '' END) AS 'R2'
,MAX(CASE WHEN fheadselfj0188='缸R 3' then fname +'('+convert(varchar(20),convert(decimal(18,0),fqty,0)) + ')' else '' END) AS 'R3'
,MAX(CASE WHEN fheadselfj0188='缸R 6' then fname +'('+convert(varchar(20),convert(decimal(18,0),fqty,0)) + ')' else '' END) AS 'R6'
,MAX(CASE WHEN fheadselfj0188='缸R 7' then fname +'('+convert(varchar(20),convert(decimal(18,0),fqty,0)) + ')' else '' end) AS 'R7'
,MAX(CASE WHEN fheadselfj0188='缸R 8' then fname +'('+convert(varchar(20),convert(decimal(18,0),fqty,0)) + ')' else '' END) as 'R8'
FROM
(
SELECT t0.*,t1.fname,ROW_NUMBER() OVER(PARTITION BY FPlanCommitDate,fheadselfj0188 ORDER BY t0.fitemid) AS x
FROM #icmo t0 inner join #t_ICItem t1 on t0.fitemid=t1.fitemid
) a
GROUP BY CONVERT(varchar(100),FPlanCommitDate,23),x
ORDER BY day
--洗掉
DROP TABLE #icmo
DROP TABLE #t_ICItem
uj5u.com熱心網友回復：
uj5u.com熱心網友回復：
這個問題其實 很簡單，懂得無損分解和自然連接就行了分解成
表R1(DAY,R1)
表R2(DAY,R2)
表R3(DAY,R3)
表R4(DAY,R4)
.......
表R8(DAY,R8)
需要查詢時SELECT R1.DAY,R1,R2.R3....R8 FROM R1,R2,R3... WHERE R1.DAY=R2.AY AND R2.DAY=R3.DAY AND .....
你想簡單了，這個世界也就簡單了
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/200478.html
標籤：應用實例
上一篇：怎樣做網路推廣？推廣途徑有哪些？
下一篇：啊啊提問百度不到