Sql group by cluase issue to get desire data

Transact-SQL
,
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)
)

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)

Query to Display all table data

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

And i m trying to get below desire data :-

so i wirte below 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

but above query displaying below data :-

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

i also tried left outer and inner join but not getting desire data..
So please help me i will be very thanfull to you........

2

Maybe this:

select [date]
      ,party_cd
      ,item_cd
      ,lot_nos
      ,sum(case when item_dimension like 'inner dia%' then qty2 else 0 end) as inner_dia
      ,sum(case when item_dimension like 'thickness%' then qty2 else 0 end) as thk
  from tbl_001
 where item_dimension like 'inner dia%'
    or item_dimension like 'thickness%'
 group by [date]
         ,party_cd
         ,item_cd
         ,lot_nos
         ,substring(item_dimension,10,10)
;
3

THANXXX bitsmed,
your query displaying below result :-

instead of my desire result

4

i updated dim. value in qty1 also as per below detail :-

t_71.png744×274 9.02 KB

i will be very thankfull to you.....

5

Thats funny, on my computer when I run:

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)
);

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)
     ,('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA','AA8/ZZ16',40.1)
     ,('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA 1','AA8/ZZ16',37.8)
     ,('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','PIERCING DIA','AA8/ZZ16',33.4)
     ,('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS','AA8/ZZ16',0.88)
     ,('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS 1','AA8/ZZ16',0.71)
     ,('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','TOTAL HEIGHT','AA8/ZZ16',9)
;

select [date]
      ,party_cd
      ,item_cd
      ,lot_nos
      ,sum(case when item_dimension like 'inner dia%' then qty2 else 0 end) as inner_dia
      ,sum(case when item_dimension like 'thickness%' then qty2 else 0 end) as thk
  from #tbl_001
 where item_dimension like 'inner dia%'
    or item_dimension like 'thickness%'
 group by [date]
         ,party_cd
         ,item_cd
         ,lot_nos
         ,substring(item_dimension,10,10)
;

DROP TABLE #TBL_001;

I get:

date                     party_cd   item_cd      lot_nos   inner_dia  thk
2016-08-20 00:00:00.000  PARTY0002  1285BRF21OP  AA8/ZZ16  40.100000  0.880000
2016-08-20 00:00:00.000  PARTY0002  1285BRF21OP  AA8/ZZ16  37.800000  0.710000

To investigate further, please try this and show us the result (especially the grp column is of importance):

select [date]
      ,party_cd
      ,item_cd
      ,lot_nos
      ,sum(case when item_dimension like 'inner dia%' then qty2 else 0 end) as inner_dia
      ,sum(case when item_dimension like 'thickness%' then qty2 else 0 end) as thk
      ,replace(substring(item_dimension,10,10),' ','¤') as grp
  from tbl_001
 where item_dimension like 'inner dia%'
    or item_dimension like 'thickness%'
 group by [date]
         ,party_cd
         ,item_cd
         ,lot_nos
         ,substring(item_dimension,10,10)
;

Updating your sample data, I would do this to get your result:

select [date]
      ,party_cd
      ,item_cd
      ,lot_nos
      ,sum(case when item_dimension like 'inner dia%' then qty1 else 0 end) as inner_dia
      ,sum(case when item_dimension like 'thickness%' then qty1 else 0 end) as thk
  from tbl_001
 where item_dimension like 'inner dia%'
    or item_dimension like 'thickness%'
 group by [date]
         ,party_cd
         ,item_cd
         ,lot_nos
         ,substring(item_dimension,10,10)
union all
select [date]
      ,party_cd
      ,item_cd
      ,lot_nos
      ,sum(case when item_dimension like 'inner dia%' then qty2 else 0 end) as inner_dia
      ,sum(case when item_dimension like 'thickness%' then qty2 else 0 end) as thk
  from tbl_001
 where item_dimension like 'inner dia%'
    or item_dimension like 'thickness%'
 group by [date]
         ,party_cd
         ,item_cd
         ,lot_nos
         ,substring(item_dimension,10,10)
;
6

Bitsmed sir thanxxxxxx to help me,
And i modfied your query to get desried result but not getting please check i will be very thakful to you:-

t_10.png794×633 44.7 KB

your modified qry :-
select [DATE],PARTY_CD ,ITEM_CD ,LOT_NOS
,sum(case when ITEM_DIMENSION like 'INNER DIA%' then ISNULL(QTY1,0) else 0 end) as INNER_DIA
,sum(case when ITEM_DIMENSION like 'PIERCING DIA%' then ISNULL(QTY1,0) else 0 end) as PIERCING_DIA
,sum(case when ITEM_DIMENSION like 'THICKNESS%' then ISNULL(QTY1,0) else 0 end) as THK
,sum(case when ITEM_DIMENSION like 'TOTAL HEI%' then ISNULL(QTY1,0) else 0 end) as TTL_HT from tbl_001
where ITEM_DIMENSION like 'INNER DIA%' or ITEM_DIMENSION like 'THICKNESS%' or
ITEM_DIMENSION like 'PIERCING DIA%' or ITEM_DIMENSION like 'TOTAL HEI%'
group by [DATE],PARTY_CD ,ITEM_CD ,LOT_NOS,substring(ITEM_DIMENSION,10,10)
union all
select [DATE],PARTY_CD ,ITEM_CD ,LOT_NOS
,sum(case when ITEM_DIMENSION like 'INNER DIA%' then ISNULL(ISNULL(QTY2,0) ,0) else 0 end) as INNER_DIA
,sum(case when ITEM_DIMENSION like 'PIERCING DIA%' then ISNULL(QTY2,0) else 0 end) as PIERCING_DIA
,sum(case when ITEM_DIMENSION like 'THICKNESS%' then ISNULL(QTY2,0) else 0 end) as THK
,sum(case when ITEM_DIMENSION like 'TOTAL HEI%' then ISNULL(QTY2,0) else 0 end) as TTL_HT from tbl_001
where ITEM_DIMENSION like 'INNER DIA%' or ITEM_DIMENSION like 'THICKNESS%' or
ITEM_DIMENSION like 'PIERCING DIA%' or ITEM_DIMENSION like 'TOTAL HEI%'
group by [DATE],PARTY_CD ,ITEM_CD ,LOT_NOS,substring(ITEM_DIMENSION,10,10)

7

Try this:

select [date]
      ,party_cd
      ,item_cd
      ,lot_nos
      ,sum(case when item_dimension='INNER DIA'    then qty1 else 0 end) as inner_dia
      ,sum(case when item_dimension='PIERCING DIA' then qty1 else 0 end) as piercing_dia
      ,sum(case when item_dimension='THICKNESS'    then qty1 else 0 end) as thk
      ,sum(case when item_dimension='TOTAL HEIGHT' then qty1 else 0 end) as ttl_ht
  from tbl_001
 where item_dimension in ('INNER DIA'
                         ,'PIERCING DIA'
                         ,'THICKNESS'
                         ,'TOTAL HEIGHT'
                         )
 group by [date]
         ,party_cd
         ,item_cd
         ,lot_nos
union all
select [date]
      ,party_cd
      ,item_cd
      ,lot_nos
      ,sum(case when item_dimension='INNER DIA 1'  then qty1 else 0 end) as inner_dia
      ,sum(case when item_dimension='PIERCING DIA' then qty2 else 0 end) as piercing_dia
      ,sum(case when item_dimension='THICKNESS 1'  then qty1 else 0 end) as thk
      ,sum(case when item_dimension='TOTAL HEIGHT' then qty2 else 0 end) as ttl_ht
  from tbl_001
 where item_dimension in ('INNER DIA 1'
                         ,'PIERCING DIA'
                         ,'THICKNESS 1'
                         ,'TOTAL HEIGHT'
                         )
 group by [date]
         ,party_cd
         ,item_cd
         ,lot_nos
union all
select [date]
      ,party_cd
      ,item_cd
      ,lot_nos
      ,sum(case when item_dimension like 'INNER DIA%'  then qty2 else 0 end) as inner_dia
      ,0 as piercing_dia
      ,sum(case when item_dimension like 'THICKNESS%'  then qty2 else 0 end) as thk
      ,0 as ttl_ht
  from tbl_001
 where item_dimension like 'INNER DIA%'
    or item_dimension like 'THICKNESS%'
 group by [date]
         ,party_cd
         ,item_cd
         ,lot_nos
         ,substring(item_dimension,10,10)
;
8

Thanx for your solution sir,
now it is working for two dim. values... can i add dim val column like qty1,qty2,qty3,qty4,qty5,qty6 etc as i explained in below screenshots :-

t_11.png745×435 13.9 KB

thanku very much for your support.......

data :-
INSERT INTO TBL_001 (DOCKET_NO,DATE,PARTY_CD ,ITEM_CD ,ITEM_DIMENSION ,LOT_NOS ,QTY1,QTY2,QTY3,QTY4,QTY5) values
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','HEIGHT', 'AA8/ZZ16', 32.10, 02.25, 33.44, 33.78,34.78),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DI', 'AA8/ZZ16', 01.01, 40.1, 23.44, 53.41,54.41),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','INNER DIA 1', 'AA8/ZZ16', 12.01, 37.8, 13.44, 45.44,46.44),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','PIERCING DI', 'AA8/ZZ16', 49.10, 33.4, 32.44, 12.14,13.14),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS', 'AA8/ZZ16', 22.01, 0.88, 43.44, 43.3, 44.3),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','THICKNESS 1', 'AA8/ZZ16', 52.01, 0.71, 33.44, 33.23,34.23),
('ZKR000816','2016-08-20','PARTY0002','1285BRF21OP','TOTAL HEIGHT','AA8/ZZ16', 28.10, 09, 43.44, 41.4, 42.4)

9

I belive this will do it:

with cte1
  as (select [date]
            ,party_cd
            ,item_cd
            ,lot_nos
            ,item_dimension
            ,row_number() over(partition by [date]
                                           ,party_cd
                                           ,item_cd
                                           ,lot_nos
                                           ,item_dimension
                               order by rn
                              )
            -1
             as rn
            ,qty
        from (select [date]
                    ,party_cd
                    ,item_cd
                    ,lot_nos
                    ,item_dimension
                    ,row_number() over(order by (select null)) as rn
                    ,qty
                from tbl_001
               unpivot (qty for details in (qty1,qty2,qty3,qty4,qty5)) as up
               where item_dimension in ('INNER DIA'
                                       ,'INNER DIA 1'
                                       ,'PIERCING DIA'
                                       ,'THICKNESS'
                                       ,'THICKNESS 1'
                                       ,'TOTAL HEIGHT'
                                       )
             ) as a
     )
    ,cte2
  as (select a.[date]
            ,a.party_cd
            ,a.item_cd
            ,a.lot_nos
            ,a.qty as inner_dia
            ,isnull(b.qty,0) as piercing_dia
            ,isnull(c.qty,0) as thk
            ,isnull(d.qty,0) as ttl_ht
            ,row_number() over(partition by a.[date]
                                           ,a.party_cd
                                           ,a.item_cd
                                           ,a.lot_nos
                                           ,a.item_dimension
                               order by a.rn
                              )
             *2-1
             as rn
        from cte1 as a
             left outer join cte1 as b
                          on b.[date]=a.[date]
                         and b.party_cd=a.party_cd
                         and b.item_cd=a.item_cd
                         and b.lot_nos=a.lot_nos
                         and b.item_dimension='PIERCING DIA'
                         and b.rn=a.rn*2
             left outer join cte1 as c
                          on c.[date]=a.[date]
                         and c.party_cd=a.party_cd
                         and c.item_cd=a.item_cd
                         and c.lot_nos=a.lot_nos
                         and c.item_dimension='THICKNESS'
                         and c.rn=a.rn
             left outer join cte1 as d
                          on d.[date]=a.[date]
                         and d.party_cd=a.party_cd
                         and d.item_cd=a.item_cd
                         and d.lot_nos=a.lot_nos
                         and d.item_dimension='TOTAL HEIGHT'
                         and d.rn=a.rn*2
       where a.item_dimension='INNER DIA'
      union all
      select a.[date]
            ,a.party_cd
            ,a.item_cd
            ,a.lot_nos
            ,a.qty as inner_dia
            ,isnull(b.qty,0) as piercing_dia
            ,isnull(c.qty,0) as thk
            ,isnull(d.qty,0) as ttl_ht
            ,row_number() over(partition by a.[date]
                                           ,a.party_cd
                                           ,a.item_cd
                                           ,a.lot_nos
                                           ,a.item_dimension
                               order by a.rn
                              )
             *2
             as rn
        from cte1 as a
             left outer join cte1 as b
                          on b.[date]=a.[date]
                         and b.party_cd=a.party_cd
                         and b.item_cd=a.item_cd
                         and b.lot_nos=a.lot_nos
                         and b.item_dimension='PIERCING DIA'
                         and b.rn=a.rn*2+1
             left outer join cte1 as c
                          on c.[date]=a.[date]
                         and c.party_cd=a.party_cd
                         and c.item_cd=a.item_cd
                         and c.lot_nos=a.lot_nos
                         and c.item_dimension='THICKNESS 1'
                         and c.rn=a.rn
             left outer join cte1 as d
                          on d.[date]=a.[date]
                         and d.party_cd=a.party_cd
                         and d.item_cd=a.item_cd
                         and d.lot_nos=a.lot_nos
                         and d.item_dimension='TOTAL HEIGHT'
                         and d.rn=a.rn*2+1
       where a.item_dimension='INNER DIA 1'
     )
select [date]
      ,party_cd
      ,item_cd
      ,lot_nos
      ,inner_dia
      ,piercing_dia
      ,thk
      ,ttl_ht
  from cte2
 order by [date]
         ,party_cd
         ,item_cd
         ,lot_nos
         ,rn
;

When you add more qty fields, just correct this line:

               unpivot (qty for details in (qty1,qty2,qty3,qty4,qty5,qty6,qty7,qty8,qty9)) as up
10

thankyou bitsmed,

i m getting error :-
Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'for'.

11

Which version of Microsoft SQL Server are you using?

I have testet on MSSQL 2008R2 (as this is the oldest version I have access to), and it works fine here.

12

I am using MS SQL SERVER 2005 Standard Edition SP2

13

Thats strange - I tried creating database in compatibility level 90 (MSSQL 2005), and it works fine here.

Do you get an error running this piece (please copy/paste without changing anything except the table reference if its not correct):

select [date]
      ,party_cd
      ,item_cd
      ,lot_nos
      ,item_dimension
      ,row_number() over(order by (select null)) as rn
      ,qty
  from tbl_001
 unpivot (qty for details in (qty1,qty2,qty3,qty4,qty5)) as up
 where item_dimension in ('INNER DIA'
                         ,'INNER DIA 1'
                         ,'PIERCING DIA'
                         ,'THICKNESS'
                         ,'THICKNESS 1'
                         ,'TOTAL HEIGHT'
                         )
;