Some query table, with his statement,
Select the CONVERT (varchar (100), t0 FPlanCommitDate, 23) as day, case when t0. Fheadselfj0188='R cylinder 1' then t1. Fname else 'end as' R1'
='cylinder, case when t0. Fheadselfj0188 R 5' then t1. The fname + '(' + convert (varchar (20), convert (a decimal (18, 0), t0. Fqty, 0)) +') 'else' end as' R5 '
, case when t0. Fheadselfj0188='R cylinder 4' then t1, fname + '(' + convert (varchar (20), convert (a decimal (18, 0), t0. Fqty, 0)) +') 'else' end as' R4
, case when t0. Fheadselfj0188='R 2 cylinder then t1, fname +' (' + convert (varchar (20), convert (a decimal (18, 0), t0. Fqty, 0)) + ') 'else' end as' R2 '
, case when t0. Fheadselfj0188='R 3 cylinder then t1, fname +' (' + convert (varchar (20), convert (a decimal (18, 0), t0. Fqty, 0)) + ') 'else' end as' R3 '
, case when t0. Fheadselfj0188='R 6 cylinder then t1, fname +' (' + convert (varchar (20), convert (a decimal (18, 0), t0. Fqty, 0)) + ') 'else' end as' R6 '
, case when t0. Fheadselfj0188='R cylinder 7 then t1, fname +' (' + convert (varchar (20), convert (a decimal (18, 0), t0. Fqty, 0)) + ') 'else' end as' R7 '
, case when t0. Fheadselfj0188='R 8 cylinder then t1, fname +' (' + convert (varchar (20), convert (a decimal (18, 0), t0. Fqty, 0)) + ') 'else' end as' the R8 '
-, t0 FQty
The from icmo t0 inner join t_ICItem t1 on t0. Fitemid=t1. Fitemid
- the from icmo t0, t_ICItem t1
Where
- t0. Fitemid=t1. Fitemid and
T0. FPlanCommitDate>=GETDATE () - 3 - and t0. Fitemid<> '
The order by FPlanCommitDate, FHeadSelfJ0188
More than conventional query them so, a lot of empty:
But I want to find out to make into this appearance, according to the dates from the first line:
Thought for a few days don't know what method, please support, thanks
CodePudding user response:
Outside in the set of a layer, add a where clause condition, the empty string are filtered outCodePudding user response:
- build table
The CREATE TABLE # icmo
(
FPlanCommitDate DATE,
Fheadselfj0188 VARCHAR (100),
Fitemid INT,
Fqty INT
)
The CREATE TABLE # t_ICItem
(
Fitemid INT,
Fname VARCHAR (100)
)
- test data
INSERT INTO # icmo VALUES (' 2020-10-30 ', 'R cylinder 1', 1, 10)
INSERT INTO # icmo VALUES (' 2020-10-30 ', 'R cylinder 3', 2117 0)
INSERT INTO # icmo VALUES (' 2020-10-30 ', 'R cylinder 6', 3280)
INSERT INTO # icmo VALUES (' 2020-10-30 ', 'R cylinder 7', 4200 0)
INSERT INTO # icmo VALUES (' 2020-10-30 ', 'R cylinder 7', 4100 0)
INSERT INTO # icmo VALUES (' 2020-10-30 ', 'R cylinder 7', 5270 0)
INSERT INTO # icmo VALUES (' 2020-10-30 ', 'R cylinder 8, 6800 0)
INSERT INTO # icmo VALUES (' 2020-10-31 ', 'R cylinder 1, 7, 10)
INSERT INTO # icmo VALUES (' 2020-10-31 ', 'R cylinder 1, 8, 20)
INSERT INTO # icmo VALUES (' 2020-10-31 ', 'R cylinder 1, 8, 30)
INSERT INTO # icmo VALUES (' 2020-10-31 ', 'cylinder R 2', 9105 0)
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)
- query (according to the first FPlanCommitDate, fheadselfj0188 grouped to produce a serial number, and then in the ranks of conversion press FPlanCommitDate + serial number aggregation, fix)
SELECT
CONVERT (varchar (100), FPlanCommitDate, 23) as day
='cylinder, MAX (CASE WHEN fheadselfj0188 R 1' then fname else 'END) as' R1'
='cylinder, MAX (CASE WHEN fheadselfj0188 R 5' then fname + '(' + convert (varchar (20), convert (a decimal (18, 0), fqty, 0)) +') 'else' END) as' R5 '
, MAX (CASE WHEN fheadselfj0188='R cylinder 4' then fname + '(' + convert (varchar (20), convert (a decimal (18, 0), fqty, 0)) +') 'else' END) as' R4
='cylinder, MAX (CASE WHEN fheadselfj0188 R 2' then fname + '(' + convert (varchar (20), convert (a decimal (18, 0), fqty, 0)) +') 'else' END) AS 'R2'
='cylinder, MAX (CASE WHEN fheadselfj0188 R 3' then fname + '(' + convert (varchar (20), convert (a decimal (18, 0), fqty, 0)) +') 'else' END) AS 'R3'
, MAX (CASE WHEN fheadselfj0188='R 6 cylinder then fname +' (' + convert (varchar (20), convert (a decimal (18, 0), fqty, 0)) + ') 'else' END) AS 'R6'
, MAX (CASE WHEN fheadselfj0188='R cylinder 7 then fname +' (' + convert (varchar (20), convert (a decimal (18, 0), fqty, 0)) + ') 'else' end) AS 'R7'
, MAX (CASE WHEN fheadselfj0188='R 8 cylinder then fname +' (' + convert (varchar (20), convert (a decimal (18, 0), fqty, 0)) + ') 'else' END) as' the R8 '
The FROM
(
SELECT t0. *, t1, fname ROW_NUMBER () OVER (PARTITION BY FPlanCommitDate, fheadselfj0188 ORDER BY t0. Fitemid) AS x
The FROM # icmo t0 inner join # t_ICItem t1 on t0. Fitemid=t1. Fitemid
) a
GROUP BY the CONVERT (varchar (100), FPlanCommitDate, 23), x
The ORDER BY day
- delete
DROP TABLE # icmo
DROP TABLE # t_ICItem
CodePudding user response: