September 5, 2016 at 9:16 am
Hi all,
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":-
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.
i also tried joins but not getting desire data So pls help me to get desire result thanxxxx
September 5, 2016 at 10:00 am
I think unless you change the design of your database, this is always going to be a bit flaky. However, this will work for the data you posted:
WITH Inners AS (SELECT * FROM ITEM_DIM WHERE ITEM_DIMENSION LIKE 'INNER%')
, Thick AS (SELECT * FROM ITEM_DIM WHERE ITEM_DIMENSION LIKE 'THICK%')
SELECT
i.DOC_DT,i.GL_CODE PARTY_CD,i.ITEM_CD,i.LOT_NOS,
CASE Nos.MyNo
WHEN 1 THEN i.QTY1
WHEN 2 THEN i.QTY2
WHEN 3 THEN i.QTY3
WHEN 4 THEN i.QTY4
WHEN 5 THEN i.QTY5
WHEN 6 THEN i.QTY6
WHEN 7 THEN i.QTY7
WHEN 8 THEN i.QTY8
END AS INNER_DIA,
CASE Nos.MyNo
WHEN 1 THEN t.QTY1
WHEN 2 THEN t.QTY2
WHEN 3 THEN t.QTY3
WHEN 4 THEN t.QTY4
WHEN 5 THEN t.QTY5
WHEN 6 THEN t.QTY6
WHEN 7 THEN t.QTY7
WHEN 8 THEN t.QTY8
END AS THK
FROM Inners i JOIN Thick t
ON (i.ITEM_DIMENSION LIKE '%1' AND t.ITEM_DIMENSION LIKE '%1')
OR (i.ITEM_DIMENSION NOT LIKE '%1' AND t.ITEM_DIMENSION NOT LIKE '%1')
CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8)) AS Nos(MyNo)
John
September 5, 2016 at 10:03 am
John Mitchell-245523 (9/5/2016)
I think unless you change the design of your database, this is always going to be a bit flaky.John
possiblly this is data that has already been "pivoted"...if so might be better to go back to source data ???
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 5, 2016 at 10:45 am
i attached below sample excel image to describe how user's software screen looking to entering data into the table through :-
September 5, 2016 at 11:10 am
Thanx John,
can i know what i need to do if i add more item dimension like "Total Height","Piercing Dia","Outer Dia" & "Height" AND more qty columns like upto 15
WITH Inners AS (SELECT * FROM tbl_001 WHERE ITEM_DIMENSION LIKE 'INNER%')
, Thick AS (SELECT * FROM tbl_001 WHERE ITEM_DIMENSION LIKE 'THICK%')
, THT AS (SELECT * FROM tbl_001 WHERE ITEM_DIMENSION LIKE 'TOTAL%')
, PD AS (SELECT * FROM tbl_001 WHERE ITEM_DIMENSION LIKE 'PIERCING%')
, OD AS (SELECT * FROM tbl_001 WHERE ITEM_DIMENSION LIKE 'OUTER%')
, HT AS (SELECT * FROM tbl_001 WHERE ITEM_DIMENSION LIKE 'HEIGH%')
September 5, 2016 at 12:51 pm
I made a modification to your table which shouldn't be hard to implement. You have a sequence number attached to some values of Item_Dimension. Put that sequence number in a second column called Item_Dimension_Seq and this will work. You can extend it for all your other dimensions by just adding more lines beginning with sum(CASE .
The primary reason for adding the new column was to deal with the odd sequencing of alternating rows. This isn't the way SQL likes to play. Reformatting using EXCEL is probably your best option.
with cte as (
select Docket_No, Doc_Dt, Gl_Code, Item_Cd, Lot_Nos, Item_Dimension,Item_Dim_Sequence, vSeq, QTY
,ROW_NUMBER() OVER(Partition BY Item_Dimension, Item_Dim_Sequence ORDER BY vSeq) as RowSeq
from Item_Dim_Modified
cross apply (values
(Qty1,1),
(Qty2,2),
(Qty3,3),
(Qty4,4),
(Qty5,5),
(Qty6,6),
(Qty7,7),
(Qty8,8),
(Qty9,9),
(Qty10,10),
(Qty11,11),
(Qty12,12),
--(Qty13,13), Superstitious?
(Qty14,14),
(Qty15,15),
(Qty16,16)) v (Qty,vSeq))
select Docket_No, Doc_Dt, Gl_Code, Item_Cd, Lot_Nos /*--, Item_Dimension,vSeq,Item_Dim_Sequence*/,vSeq+(ITEM_DIM_SEQUENCE*.001) as MatchSeq
,sum(CASE WHEN Item_Dimension like 'Inner%' then QTY else null end) as Inner_Dia
,sum(CASE WHEN Item_Dimension like 'Thick%' then QTY else null end) as Thick
from cte
group by Docket_No, Doc_Dt, Gl_Code, Item_Cd, Lot_Nos, Item_Dim_Sequence, vSeq+(ITEM_DIM_SEQUENCE*.001)
order by Docket_No, Doc_Dt, Gl_Code, Item_Cd, Lot_Nos,vSeq+(ITEM_DIM_SEQUENCE*.001)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply