SQL Query using Stuff keyword (Network Steve Forum)

SQL Query using Stuff keyword

CREATE TABLE BILL_DETAIL
([objid] int,[x_billable_to] varchar(19), [x_bill_quantity] int,
[x_billable_yn] int, [x_bill_rate] int, [COST_TYPE] varchar(19) )
;

INSERT INTO BILL_DETAIL
([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE])
VALUES
(1, 'Customer', 3, 1, 20,'Parking'),
(1, 'Customer', 1, 1, 25,'Toll'),
(3, 'Customer', 2, 1, 20,'Parking')

;

Can you please tell me how to get records using stuff keyword as above i want to query using where condition with

where objid=1 and should frame output as Parking, Toll only 1 input parameter need to given.

June 17th, 2015 8:49am

HI,

DECLARE @tbl TABLE ([objid] int,[x_billable_to] varchar(19), [x_bill_quantity] int,
     [x_billable_yn] int, [x_bill_rate] int,   [COST_TYPE] varchar(19) )
;

INSERT INTO @tbl
([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE])
VALUES
(1, 'Customer', 3, 1, 20,'Parking'),
(1, 'Customer', 1, 1, 25,'Toll'),
(3, 'Customer', 2, 1, 20,'Parking')

SELECT objid,SUM(x_bill_quantity) x_bill_quantity,SUM(x_bill_quantity) x_bill_quantity,MAX(x_billable_yn) x_billable_yn,AVG(x_bill_rate) x_bill_rate,
STUFF((select ', ' + [COST_TYPE]
from @tbl
WHERE objid = c1.objid
for xml path('')),1,2,'')  
FROM @tbl c1
GROUP BY objid

Free Windows Admin Tool Kit Click here and download it now

June 17th, 2015 9:30am

CREATE TABLE BILL_DETAIL
([objid] int,[x_billable_to] varchar(19), [x_bill_quantity] int,
     [x_billable_yn] int, [x_bill_rate] int,   [COST_TYPE] varchar(19) )
;

INSERT INTO BILL_DETAIL
([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE])
VALUES
(1, 'Customer', 3, 1, 20,'Parking'),
(1, 'Customer', 1, 1, 25,'Toll'),
(3, 'Customer', 2, 1, 20,'Parking')

;
select * from BILL_DETAIL

SELECT t1.[objid],
       Stuff(( SELECT ',' + [COST_TYPE]
           FROM BILL_DETAIL t2
          WHERE t2.[objid] = t1.[objid]             
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
  FROM BILL_DETAIL t1
  WHERE t1.[objid]=1
 GROUP BY t1.[objid]



drop table BILL_DETAIL;

June 17th, 2015 9:35am

Thanks for the reply

Can you please suggest using where objid=1 in same query in Sqlserver if there are 3 records i need to get 2 records and it should frame with Parking, Toll

Free Windows Admin Tool Kit Click here and download it now

June 17th, 2015 9:37am

Thanks for your reply

My actual requirement is with using 3 tables and need to get the data

CREATE TABLE BILL_DETAIL
([objid] int,[x_billable_to] varchar(19), [x_bill_quantity] int,
[x_billable_yn] int, [x_bill_rate] int, [COST_TYPE] varchar(19) , [BILL2SUBCASE] int )

INSERT INTO BILL_DETAIL
([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE], [BILL2SUBCASE])

VALUES
(101, 'Customer', 3, 1, 20,'Parking',11),
(102, 'Customer', 1, 1, 25,'Car',12),
(103, 'Customer', 2, 1, 20,'Toll',11)

;

CREATE TABLE SUBCASE
([objid] int, [SUBCASE2ASSIGN] int,
)

INSERT INTO SUBCASE
([objid], [SUBCASE2ASSIGN])

VALUES
(11, 1),
(12, 2)

;

CREATE TABLE ASSIGN
([objid] int
)

INSERT INTO ASSIGN
([objid])

VALUES
(1),
(2)

;

using following query iam getting 2 records but i want to get only 1 record and cost type should get appending other record data as Parking,Toll

select BILL_DETAIL.COST_TYPE

from ASSIGN

inner join SUBCASE on SUBCASE.SUBCASE2ASSIGN=ASSIGN.objid
inner join BILL_DETAIL on BILL_DETAIL.BILL2SUBCASE=SUBCASE.objid
where ASSIGN.objid=1

Please suggest using stuff key word and group by i will only parameter for query

with the above schema i want to Pass parameter ASSIGN.objid=1 using inner relations with

select

billde

and need to the output with Cost Type Parking,Toll

please suggest me how to use it with 3 tables .

with the above schema i want to Pass parameter ASSIGN.objid=1 using inner relations with

select

billde

and need to the output with Cost Type Parking,Toll

please suggest me how to use it with 3 tables .

June 17th, 2015 10:32am

CREATE TABLE BILL_DETAIL
([objid] int,[x_billable_to] varchar(19), [x_bill_quantity] int,
     [x_billable_yn] int, [x_bill_rate] int,   [COST_TYPE] varchar(19) , [BILL2SUBCASE] int )


INSERT INTO BILL_DETAIL
([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE], [BILL2SUBCASE])

VALUES(101, 'Customer', 3, 1, 20,'Parking',11),
(102, 'Customer', 1, 1, 25,'Car',12),
(103, 'Customer', 2, 1, 20,'Toll',11);

CREATE TABLE SUBCASE([objid] int, [SUBCASE2ASSIGN] int )
INSERT INTO SUBCASE([objid], [SUBCASE2ASSIGN])
VALUES(11,  1),(12, 2);

CREATE TABLE  ASSIGN([objid] int)
INSERT INTO ASSIGN([objid])VALUES(1),(2);

;


;With mycte as (
select ASSIGN.objid, BILL_DETAIL.COST_TYPE

from ASSIGN

inner join SUBCASE on SUBCASE.SUBCASE2ASSIGN=ASSIGN.objid
inner join BILL_DETAIL on BILL_DETAIL.BILL2SUBCASE=SUBCASE.objid
where ASSIGN.objid=1

)


SELECT 
       Stuff(( SELECT ',' + [COST_TYPE]
           FROM mycte t2
          WHERE t2.[objid] = t1.[objid]             
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS Names
  FROM mycte t1
  WHERE t1.[objid]=1
 GROUP BY t1.[objid]
 


drop table BILL_DETAIL,ASSIGN, SUBCASE;

Free Windows Admin Tool Kit Click here and download it now

June 17th, 2015 10:41am

Jingyang

I will pass only parameter to the query as Assign.objid=1

can you send me query that retrieves data as Parking, Toll as Cost Type

June 17th, 2015 10:45am

declare @objid int=1

;With mycte as (
select ASSIGN.objid, BILL_DETAIL.COST_TYPE
from ASSIGN
inner join SUBCASE on SUBCASE.SUBCASE2ASSIGN=ASSIGN.objid
inner join BILL_DETAIL on BILL_DETAIL.BILL2SUBCASE=SUBCASE.objid


where ASSIGN.objid=   @objid


)




SELECT  Stuff(( SELECT ',' + [COST_TYPE]
           FROM mycte t2
          WHERE t2.[objid] = t1.[objid]             
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS [Cost Type]
  FROM mycte t1
 GROUP BY t1.[objid]

Marked as answer by Dkrishna 16 hours 21 minutes ago

Free Windows Admin Tool Kit Click here and download it now

June 17th, 2015 10:51am

Thanks for the reply

i am not using stored procedure i need to pass parameter normally as Assign.objid=1

Can i use your answer as it is in sql server starting from

;With mycte as ( ........

,..........

.................................

to GROUP BY t1.[objid] as ending???????

if so can you please suggest me how to use ;with key word in sql select query as starting line.

June 17th, 2015 11:19am

YES.

You can replace @objid with hard coded value 1 in the code.

Marked as answer by Dkrishna 16 hours 21 minutes ago
Unmarked as answer by Dkrishna 16 hours 21 minutes ago
Marked as answer by Dkrishna 16 hours 21 minutes ago

Free Windows Admin Tool Kit Click here and download it now

June 17th, 2015 11:23am

Thanks a lot ..your query works great.

i have one more issue with the query iam unable run by using order by select null in query can you please help me how to use it .

iam getting error at with statement .can you please help me how to run this following query

SELECT * FROM (SELECT row_number() over ( order by (select null) ) rn,
With mycte as (
select ASSIGN.objid, BILL_DETAIL.COST_TYPE
from ASSIGN
inner join SUBCASE on SUBCASE.SUBCASE2ASSIGN=ASSIGN.objid
inner join BILL_DETAIL on BILL_DETAIL.BILL2SUBCASE=SUBCASE.objid

where ASSIGN.objid= 1

)

SELECT Stuff(( SELECT ',' + [COST_TYPE]
FROM mycte t2
WHERE t2.[objid] = t1.[objid]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS [Cost Type]
FROM mycte t1
GROUP BY t1.[objid]

) dt
WHERE rn BETWEEN 0 AND 100

June 18th, 2015 2:27am

Thanks a lot ..your query works great.

i have one more issue with the query iam unable run by using order by select null in query can you please help me how to use it .

iam getting error at with statement .can you please help me how to run this following query

SELECT * FROM (SELECT row_number() over ( order by (select null) ) rn,
With mycte as (
select ASSIGN.objid, BILL_DETAIL.COST_TYPE
from ASSIGN
inner join SUBCASE on SUBCASE.SUBCASE2ASSIGN=ASSIGN.objid
inner join BILL_DETAIL on BILL_DETAIL.BILL2SUBCASE=SUBCASE.objid

where ASSIGN.objid= 1

)

SELECT Stuff(( SELECT ',' + [COST_TYPE]
FROM mycte t2
WHERE t2.[objid] = t1.[objid]
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') AS [Cost Type]
FROM mycte t1
GROUP BY t1.[objid]

) dt
WHERE rn BETWEEN 0 AND 100

Free Windows Admin Tool Kit Click here and download it now

June 18th, 2015 3:00am

Thanks for the reply but i my query

SELECT * FROM (SELECT row_number() over ( order by (select null) ) rn,

statement is mandatory and i will pass rn passed as parameters as 0 and 100 from the code side

end of the statement should be

) dt
WHERE rn BETWEEN 0 AND 100

is there any chance editing the query make it possible to run with the my requirement

right now iam getting error at with statement ..

Please help

Thanks

krishna

June 18th, 2015 3:09am

Hi Dkrishna,

What is the rn supposed to be here? The query below would only return 1.

 SELECT row_number() over ( order by (select null) ) rn

If the statement if mandatory, use derived table instead.

SELECT * FROM (SELECT row_number() over ( order by (select null) ) rn, 
(SELECT Stuff(( SELECT ',' + [COST_TYPE]
           FROM (select ASSIGN.objid, BILL_DETAIL.COST_TYPE
from ASSIGN
inner join SUBCASE on SUBCASE.SUBCASE2ASSIGN=ASSIGN.objid
inner join BILL_DETAIL on BILL_DETAIL.BILL2SUBCASE=SUBCASE.objid
where ASSIGN.objid=  1) t2
          WHERE t2.[objid] = t1.[objid]             
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS [Cost Type]
  FROM (select ASSIGN.objid, BILL_DETAIL.COST_TYPE
from ASSIGN
inner join SUBCASE on SUBCASE.SUBCASE2ASSIGN=ASSIGN.objid
inner join BILL_DETAIL on BILL_DETAIL.BILL2SUBCASE=SUBCASE.objid
where ASSIGN.objid=  1) t1
 GROUP BY t1.[objid] ) AS [Cost Type]
                                 ) dt
 WHERE rn BETWEEN 0 AND 100

But errors can happen when the STUFF FOR XML subquery returns more than one row.

Free Windows Admin Tool Kit Click here and download it now

June 18th, 2015 3:17am

If the there are 1000s of records we will return 1st 100 records and then using paging class we will retrieve next 100 records if required

i just given sample data to retrieve data as per my requirement.

Please help how we can do it.

June 18th, 2015 3:21am

Hi Krishna,

May I know what you're about to page, page the below query result?

;With mycte as (
select ASSIGN.objid, BILL_DETAIL.COST_TYPE
from ASSIGN
inner join SUBCASE on SUBCASE.SUBCASE2ASSIGN=ASSIGN.objid
inner join BILL_DETAIL on BILL_DETAIL.BILL2SUBCASE=SUBCASE.objid
where ASSIGN.objid=  1
)
SELECT  Stuff(( SELECT ',' + [COST_TYPE]
           FROM mycte t2
          WHERE t2.[objid] = t1.[objid]             
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS [Cost Type]
  FROM mycte t1
 GROUP BY t1.[objid]

Free Windows Admin Tool Kit Click here and download it now

June 18th, 2015 3:29am

In the Sample data i just given to get only 1 record but i have a requirement that if there are 1000 records i need to get 1st 100 records and next 100 like wise.....

Can you please send me excutable query in such a way that it can run using following statements

iam trying in following method but it getting error at with statement

June 18th, 2015 3:36am

Hi Dkrishna,

Please see below sample.

;With mycte as (
select ASSIGN.objid, BILL_DETAIL.COST_TYPE,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn --specify a real column here, order by select null is not reliable 
from ASSIGN
inner join SUBCASE on SUBCASE.SUBCASE2ASSIGN=ASSIGN.objid
inner join BILL_DETAIL on BILL_DETAIL.BILL2SUBCASE=SUBCASE.objid
where ASSIGN.objid=  1
)
SELECT  Stuff(( SELECT ',' + [COST_TYPE]
           FROM mycte t2
          WHERE t2.[objid] = t1.[objid]             
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS [Cost Type]
  FROM mycte t1
  WHERE rn BETWEEN 0 AND 100
 GROUP BY t1.[objid]
               
GO

DECLARE @PageNo INT = 1

;With mycte as (
select ASSIGN.objid, BILL_DETAIL.COST_TYPE,
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) rn --specify a real column here, order by select null is not reliable 
from ASSIGN
inner join SUBCASE on SUBCASE.SUBCASE2ASSIGN=ASSIGN.objid
inner join BILL_DETAIL on BILL_DETAIL.BILL2SUBCASE=SUBCASE.objid
where ASSIGN.objid=  1
)
SELECT  Stuff(( SELECT ',' + [COST_TYPE]
           FROM mycte t2
          WHERE t2.[objid] = t1.[objid]             
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS [Cost Type]
  FROM mycte t1
  WHERE rn BETWEEN  1+(@PageNo-1)*100 AND @PageNo*100
 GROUP BY t1.[objid]

If you have any question, feel free to let me know.

Free Windows Admin Tool Kit Click here and download it now

June 18th, 2015 3:43am

This topic is archived. No further replies will be accepted.