lundi 5 septembre 2016

MS SQL Union all & Group by clause not working

I have a table as per below detail:-

CREATE TABLE ITEM_DIM
(DOCKET_NO VARCHAR(9),DOC_DT DATETIME,GL_CODE VARCHAR(10),ITEM_CD VARCHAR(15),
ITEM_DIMENSION VARCHAR(100),LOT_NOS VARCHAR(15),
QTY1 DECIMAL(14,6),QTY2 DECIMAL(14,6),QTY3 DECIMAL(14,6),QTY4 DECIMAL(14,6),QTY5 DECIMAL(14,6),
QTY6 DECIMAL(14,6),QTY7 DECIMAL(14,6),QTY8 DECIMAL(14,6),QTY9 DECIMAL(14,6),QTY10 DECIMAL(14,6)
,QTY11 DECIMAL(14,6),QTY12 DECIMAL(14,6),QTY14 DECIMAL(14,6),QTY15 DECIMAL(14,6),QTY16 DECIMAL(14,6))

inserted statement to insert data into table :-

INSERT INTO ITEM_DIM (DOCKET_NO,DOC_DT,GL_CODE ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY1,QTY2,QTY3,QTY4,QTY5,QTY6,QTY7,QTY8) values
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA', 'AA8/ZZ16', 40.1,40,39.91,39.92,39.93,39.94,39.95,39.96),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA 1', 'AA8/ZZ16', 37.8,37.71,37.72,37.73,37.74,37.75,37.76,37.77),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS ', 'AA8/ZZ16', 0.88,0.72,0.73,0.74,0.75,0.76,0.77,0.77),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS 1', 'AA8/ZZ16', 0.71,0.72,0.73,0.74,0.75,0.76,0.77,0.78)

Select statement to display data :-
SELECT DOCKET_NO,DOC_DT,GL_CODE PARTY_CD,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY1,QTY2,QTY3,QTY4,QTY5,QTY6,QTY7,QTY8 FROM ITEM_DIM

i m trying to get result as per attached image "Desire_Result":-
enter image description here

so i write below query to get desire result:-

SELECT DOC_DT,PARTY_CD,ITEM_CD,LOT_NOS,INNER_DIA,THK FROM
(
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY1 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY2 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY3 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY4 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY5 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY6 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY7 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,QTY8 INNER_DIA,0 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY1 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY2 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY3 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY4 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY5 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY6 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY7 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')
UNION ALL
SELECT DOC_DT,GL_CODE PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY8 THK FROM ITEM_DIM
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')) Z 

but my query displaying result as per attached image "My_Qry_Result" instead of desire result.

enter image description here

i also tried joins but not getting desire data So pls help me to get desire result thanxxxx

Aucun commentaire:

Enregistrer un commentaire