I im trying to append results from select inside cursor to a temp table or temp variable. Please advice me how to do it in better way. My code works but insert function has a mistake which I cant determine. Also I need to use UNIOn to add a row under table which contains count for each numerical column under select result. My sample code is below, and my tables code is also under:
DECLARE @cnt INTEGER
SET @cnt=1
DECLARE @Ids INTEGER
CREATE TABLE #output_tab
(
"Num" INTEGER,
"Name" VARCHAR(4),
"P1" INTEGER,
"P2" INTEGER,
"P3" INTEGER,
"P4" INTEGER,
"D1" INTEGER,
"D2" INTEGER,
"D3" INTEGER,
"D4" INTEGER
)
DECLARE build_table CURSOR
FOR
select distinct Id from #tab1;
OPEN build_table
FETCH NEXT FROM build_table INTO
@Ids;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #output_tab (
SELECT @cnt, e.Name, f.P1, f.P2, f.P3,f.P4, l.D1 , l.D2, l.D3, l.D4
FROM #tab1 e
LEFT JOIN (
SELECT f.*, ROW_NUMBER() OVER (PARTITION BY f.Id ORDER BY (SELECT NULL)) AS seqnum1
FROM #tab2 f
) f ON f.Id = e.Id
LEFT JOIN (
SELECT l.*, ROW_NUMBER() OVER (PARTITION BY l.Id ORDER BY (SELECT NULL)) AS seqnum2
FROM #tab3 l
) l ON l.Id = e.Id AND (f.seqnum1 = l.seqnum2 OR f.seqnum1 IS NULL)
where e.Id=@Ids);
SET @cnt = @cnt 1
FETCH NEXT FROM build_table INTO
@Ids;
END;
CLOSE build_table;
DEALLOCATE build_table;
select * from #output_tab
drop table #output_tab
My tables code
CREATE TABLE #tab1 (
"Id" INTEGER,
"Name" VARCHAR(7),
"Status" VARCHAR(8)
);
INSERT INTO #tab1
("Id", "Name", "Status")
VALUES
('101', 'Samsung', 'Enabled'),
('102', 'Huawei', 'Disabled'),
('103', 'Nokia', 'Enabled'),
('104', 'Sony', 'Enabled');
CREATE TABLE #tab2 (
"Id" INTEGER,
"P1" decimal(18,9),
"P2" INTEGER,
"P3" decimal(18,9),
"P4" decimal(18,9)
);
INSERT INTO #tab2
("Id", "P1", "P2", "P3", "P4")
VALUES
('101', '2', '5', '45', '56'),
('101', '14', '3', '78', '7'),
('101', '33', '2', '32', '34'),
('102', '52', '1', '14', '61'),
('102', '71', '0', '60', '88'),
('103', '87.2', '2', '35.6', '84.6'),
('103', '104.8', '67', '32.2', '96.4'),
('103', '122.4', '3', '28.8', '108.2'),
('103', '140', '5', '25.4', '120'),
('104', '157.6', '7', '22', '131.8');
CREATE TABLE #tab3 (
"Id" INTEGER,
"D1" INTEGER,
"D2" INTEGER,
"D3" INTEGER,
"D4" INTEGER
);
INSERT INTO #tab3
("Id", "D1", "D2", "D3", "D4")
VALUES
('101', '33', '2', '32', '34'),
('102', '52', '1', '14', '61'),
('102', '71', '0', '60', '88'),
('102', '90', '32', '106', '115'),
('102', '109', '14', '32', '34'),
('103', '52', '1', '32', '34'),
('103', '71', '0', '14', '61'),
('104', '90', '32', '60', '88'),
('104', '109', '14', '106', '115');
CREATE TABLE #tab4 (
"Id" INTEGER,
"E1" INTEGER,
"E2" INTEGER,
"E3" INTEGER,
"E4" INTEGER
);
INSERT INTO #tab4
("Id", "E1", "E2", "E3", "E4")
VALUES
('101', '43', '22', '87', '90'),
('101', '62', '29', '23', '34'),
('101', '79', '21', '76', '43'),
('101', '92', '200', '11', '0'),
('102', '54', '779', '12', '484'),
('103', '592', '41', '326', '334'),
('103', '731', '10', '134', '651'),
('104', '920', '332', '650', '868'),
('104', '16', '143', '1026', '1145');
Im using cursor here cuse I need to achive such a view of the output result. Assuming that blanks are filled with Null Image of my desired output
CodePudding user response：
T-SQL is not really the place to do this kind of manipulation for display purposes. You should use your presentation layer to do this.
Be that as it may, you can use the following
ROW_NUMBERto join the three tables together.tab2andtab3need aFULL JOINbetweeen them, thenLEFT JOIN that back totab1`.LAGandNULLIFto null outIdof rows of the same groupGROUPING SETSto generate rollup rows.
SELECT
Id = NULLIF(t1.Id , LAG(t1.Id ) OVER (ORDER BY t1.Id)),
Name = CASE WHEN GROUPING(t2.P1) = 1
THEN 'Total'
ELSE NULLIF(t1.Name, LAG(t1.Name) OVER (ORDER BY t1.Id))
END,
P1 = SUM(t2.P1),
P2 = SUM(t2.P2),
P3 = SUM(t2.P3),
P4 = SUM(t2.P4),
D1 = SUM(t3.D1),
D2 = SUM(t3.D2),
D3 = SUM(t3.D3),
D4 = SUM(t3.D4)
FROM #tab1 t1
LEFT JOIN (
SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY t2.Id ORDER BY (SELECT NULL))
FROM #tab2 t2
) t2
FULL JOIN (
SELECT *,
rn = ROW_NUMBER() OVER (PARTITION BY t3.Id ORDER BY (SELECT NULL))
FROM #tab3 t3
) t3 ON t3.Id = t2.Id
AND t3.rn = t2.rn
ON ISNULL(t2.Id, t3.Id) = t1.Id
GROUP BY GROUPING SETS (
(
t1.Id, t1.Name, t2.P1, t2.P2, t2.P3, t2.P4, t3.D1, t3.D2, t3.D3, t3.D4
),
(
t1.Id, t1.Name
)
);