Query

Question

User-797751191 posted

Hi

  I have below data 

1	30	05/04/19	C	7	05/04/19	ABC 	200	27400
2	31	05/04/19	C	12	05/04/19	XYZ	193	28950
3	32	05/04/19	C	14	05/04/19	YYY     2000	26620
3	32	05/04/19	C	14	05/04/19	YYY     4000    10000

I want that it should be displayed like below using Rollup or some other way

1	30	05/04/19	C	7	05/04/19	ABC 	200	27400
2	31	05/04/19	C	12	05/04/19	XYZ	193	28950
3	32	05/04/19	C	14	05/04/19	YYY     2000	26620
                                                                        4000    10000

Thanks

Answer 1

User-719153870 posted

Hi jsshivalik,

AFAIK, the ROLLUP clause is not for this kind of requirement.

Please refer to below demo:

create table AAA(id int,age int,sdate date,itype varchar(10),anum int,edate date,name varchar(20),num int,snum int)
insert into AAA values(1,30,'2019-05-04','C',7,'2019-05-04','ABC',200,27400),
(2,31,'2019-05-04','C',12,'2019-05-04','XYZ',193,28950),(3,32,'2019-05-04','C',14,'2019-05-04','YYY',2000,26620),
(3,32,'2019-05-04','C',14,'2019-05-04','YYY',4000,10000)

select * from AAA
SELECT
	CASE WHEN RR = 1 THEN id ELSE null END id,
	CASE WHEN RR = 1 THEN age ELSE null END age,
	CASE WHEN RR = 1 THEN sdate ELSE null END sdate,
	CASE WHEN RR = 1 THEN itype ELSE null END itype,
	CASE WHEN RR = 1 THEN anum ELSE null END anum,
	CASE WHEN RR = 1 THEN edate ELSE null END edate,
	CASE WHEN RR = 1 THEN name ELSE null END name,
	num,
	snum
FROM
	(SELECT
	(ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)) [RR], *
	FROM AAA) SUBQUERY1
drop table AAA

Below is the result of this demo:

Here's a similar question from MSDN.

In addition, if you are interested about PARTITION BY, please check SQL PARTITION BY Clause overview.

Best Regard,

Yang Shen

Answer 2

User-797751191 posted

Hi Yand

   Wht changes will be reqd to be done in the below code

SELECT M.DocNum AS 'AP Inv. #', M.DocDate as 'Date',M.DocStatus,M.NumAtCard as 'Bill No. & Dt.', M.TaxDate as 'Document Date',
M.CardName as 'Vendor Name',A.GSTRegnNo'GST No',P.[Location] 'Location',
Sum(L.Quantity) as 'Quantity',
Sum(L.LineTotal) as 'Base Amt.(Rs.)',Max(L.VatPrcnt)'Tax Code'

FROM Test0 M LEFT OUTER JOIN Test1 L on L.DocEntry=M.DocEntry
LEFT JOIN Test2 A on M.CardCode=A.CardCode 
INNER JOIN Test3 P on L.[LocCode]=P.
 

WHERE (M.DocDate >= '2019/04/05' AND M.DocDate <= '2019/04/05'  )

GROUP BY
M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location],L.TaxCode
Thanks

Answer 3

User-719153870 posted

Hi jsshivalik,

It's hard to say where we need to change since this code is not complete.

Assume the result of this query is the 1st dataset you provided at the beginning, then i will suggest you to select it(the result of this query) into a temp table, please refer to SQL SELECT INTO Examples for more information.

In your case, it should be like below(didn't test but it shall work):

select * into #AAA from (SELECT M.DocNum AS 'AP Inv. #', M.DocDate as 'Date',M.DocStatus,M.NumAtCard as 'Bill No. & Dt.', M.TaxDate as 'Document Date',
M.CardName as 'Vendor Name',A.GSTRegnNo'GST No',P.[Location] 'Location',
Sum(L.Quantity) as 'Quantity',
Sum(L.LineTotal) as 'Base Amt.(Rs.)',Max(L.VatPrcnt)'Tax Code'

FROM Test0 M LEFT OUTER JOIN Test1 L on L.DocEntry=M.DocEntry
LEFT JOIN Test2 A on M.CardCode=A.CardCode 
INNER JOIN Test3 P on L.[LocCode]=P.
 

WHERE (M.DocDate >= '2019/04/05' AND M.DocDate <= '2019/04/05'  )

GROUP BY
M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location],L.TaxCode)a
After
 that, use the query i provided and change the table name and clomun names in it:
SELECT
	CASE WHEN RR = 1 THEN id ELSE null END id,
	CASE WHEN RR = 1 THEN age ELSE null END age,
	CASE WHEN RR = 1 THEN sdate ELSE null END sdate,
	CASE WHEN RR = 1 THEN itype ELSE null END itype,
	CASE WHEN RR = 1 THEN anum ELSE null END anum,
	CASE WHEN RR = 1 THEN edate ELSE null END edate,
	CASE WHEN RR = 1 THEN name ELSE null END name,
	num,
	snum
FROM
	(SELECT
	(ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)) [RR], *
	FROM #AAA) SUBQUERY1
PS.
 why not use where M.DocDate = '2019/04/05' instead of WHERE (M.DocDate >= '2019/04/05' AND M.DocDate <= '2019/04/05' ).
Best Regard,
Yang Shen

Answer 4

User-797751191 posted

Hi Yang

  Why we need to write a at the end

M.DocEntry,M.DocNum,M.DocDate,M.DocStatus,M.NumAtCard,M.TaxDate,M.CardName,A.GSTRegnNo,P.[Location],L.TaxCode)a

Thanks