Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL group by clause issue

Author  Topic 

neellotus
Starting Member

15 Posts

Posted - 2016-09-04 : 08:51:20

Hi all,

kindly find the below detail of my table:-

1. Query to CREATE TABLE

CREATE TABLE TBL_001

(
DOCKET_NO VARCHAR(9),
DATE DATETIME,
PARTY_CD VARCHAR(10),
ITEM_CD VARCHAR(15),
ITEM_DIMENSION VARCHAR(100),
LOT_NOS VARCHAR(15),
QTY1 DECIMAL(14,6),
QTY2 DECIMAL(14,6)
)

2. Query to INSERT DATA

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','HEIGHT','AA8/ZZ16',2.25)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA','AA8/ZZ16',40.1)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA 1','AA8/ZZ16',37.8)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','PIERCING DIA','AA8/ZZ16',33.4)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS','AA8/ZZ16',0.88)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS 1','AA8/ZZ16',0.71)

INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2)
VALUES('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','TOTAL HEIGHT','AA8/ZZ16',9)


3. Query to Display table data

SELECT DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY2 FROM TBL_001

4. my query to get desire data :-

SELECT DATE,PARTY_CD,ITEM_CD,LOT_NOS,INNER_DIA,THK FROM
(SELECT DATE,PARTY_CD,ITEM_CD,LOT_NOS,QTY2 INNER_DIA,0 THK FROM TBL_001
WHERE ITEM_DIMENSION IN ('INNER DIA','INNER DIA 1')
UNION ALL
SELECT DATE,PARTY_CD,ITEM_CD,LOT_NOS,0 INNER_DIA, QTY2 THK FROM TBL_001
WHERE ITEM_DIMENSION IN ('THICKNESS','THICKNESS 1')) Z WHERE LOT_NOS='AA8/ZZ16'
GROUP BY DATE,PARTY_CD,ITEM_CD,LOT_NOS,INNER_DIA,THK

Result of above query :-

DATE PARTY_CD ITEM_CD LOT_NOS INNER_DIA THK
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 0 0.71
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 0 0.88
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 37.8 0
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 40.1 0


but i m trying to get below result :-
DATE PARTY_CD ITEM_CD LOT_NOS INNER_DIA THK
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 37.8 0.71
20-08-2016 PARTY0002 1285BRF21OP AA8/ZZ16 40.1 0.88


i also tried inner join and left join but not getting desire result so please help me i will be very thankfull to you...

   

- Advertisement -