NEed help for SQLServer Query

Question

NEed help for SQLServer Query

d.krishnap

Valued Member

Points: 65
More actions
January 26, 2015 at 10:04 am

#314065

Can anyone pls modify following query for SQlserver
can you pls run following query at http://sqlfiddle.com/#!3/c9ec9f/4 i tried created and tables and fields and data for running following query in the above link. i just need alternative keyword need to be used instead of ListAgg (i.e Stuff keyword need to be used)in sqlserver query and Case condition need to be used instead of Decode and groupby and orderby need to used in proper way to run following. iam able to run following query in oracle but struggling to run in sqlserver database. pls help me.
http://sqlfiddle.com/#!3/c9ec9f/4
SELECT * FROM (SELECT 1 rn,
SUM(BILL_DETAIL.x_bill_quantity) as BILL_QUANTITY,
MIN(BILL_DETAIL.x_billable_to) as BILLABLE_TO,
MIN(BILL_DETAIL.x_billable_yn) as BILLABLE_YN,
AVG(BILL_DETAIL.x_bill_rate) as BILL_RATE,
LISTAGG(BILL_DETAIL.objid, ',') WITHIN GROUP(ORDER BY BILL_DETAIL.objid) as ID_LIST
FROM BILL_DETAIL
WHERE (1=1)
GROUP BY (DECODE(x_billable_yn, 1, 'Billable', 'Non-Billable') + ',' + x_billable_to + ',' + COST_TYPE + ',' + x_bill_rate)
ORDER BY DECODE(x_billable_yn, 1, 'Billable', 'Non-Billable') + ',' + x_billable_to + ',' + COST_TYPE + ',' + x_bill_rate
)dt
WHERE rn BETWEEN 0 AND 1

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Table definitions please, some sample data and what is it supposed to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

d.krishnap Valued Member Points: 65 More actions · Answer 2

use this link http://sqlfiddle.com/#!3/c9ec9f/4

Table defination fields and some sample data is created in the above link for running the above query.

Please test it and send me the converted query of sqlserver.

Iam struggling with the keywords of Stuff and groupby .

Pls help

Thanks

Krishna

d.krishnap Valued Member Points: 65 More actions · Answer 3

Open the link http://sqlfiddle.com/#!3/c9ec9f/4 and update the query and click on the Runsql and it will give the data if the query executes without error

Jeff Moden SSC Guru Points: 1004701 More actions · Answer 4

I don't do links for two reasons... 1) is that I don't actually know what's there and don't want to take the chance going there and 2) is that link could go away in the future which would make this thread pretty useless.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

d.krishnap Valued Member Points: 65 More actions · Answer 5

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'),

(2, 'Customer', 1, 1, 25,'Toll'),

(3, 'Customer', 2, 1, 20,'Parking')

and for Column List_IDs i need the values in the format of 1,2,3

use above values and

Sean Lange SSC Guru Points: 286609 More actions · Answer 6

Jeff Moden (1/26/2015)
I don't do links for two reasons... 1) is that I don't actually know what's there and don't want to take the chance going there and 2) is that link could go away in the future which would make this thread pretty useless.

I understand your sentiment here Jeff but sqlfiddle.com is pretty safe. The link going away though is a concern that I totally agree with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

d.krishnap (1/26/2015)
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'),
(2, 'Customer', 1, 1, 25,'Toll'),
(3, 'Customer', 2, 1, 20,'Parking')
and for Column List_IDs i need the values in the format of 1,2,3
use above values and

Here is the issue. We have a sql fiddle which has a query that runs. Then we have another copy of the ddl and sample data (thanks for that). What we don't have is a clear picture of what you are trying to do here. I think what you are asking is how to create a comma separated list of values. Check out this article which explains is quite nicely. http://www.sqlservercentral.com/articles/comma+separated+list/71700/%5B/url%5D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

d.krishnap Valued Member Points: 65 More actions · Answer 8

Thanks for the reply

Question

You cannot vote on your own post

0

if there 3 records in bill_detail table like this

INSERT INTO DETAIL

([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE])

VALUES

(1, 'Customer', 3, 1, 20,'Parking'),

(2, 'Customer', 1, 1, 25,'Toll'),

(3, 'Customer', 2, 1, 20,'Parking')

then query should return only 2 records as the costype(Parking),Rate(20),Billto(Customer)....its need to be done group by and 2 records should be get ...

1st record should be (2, 'Customer', 1, 1, 25,'Toll'), in the ID_list value=2

2nd record is sum of 1st and 3rd record and ID_list=1,3

Sean Lange SSC Guru Points: 286609 More actions · Answer 9

d.krishnap (1/27/2015)
Thanks for the reply
Question
You cannot vote on your own post
0
if there 3 records in bill_detail table like this
INSERT INTO DETAIL
([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE])
VALUES
(1, 'Customer', 3, 1, 20,'Parking'),
(2, 'Customer', 1, 1, 25,'Toll'),
(3, 'Customer', 2, 1, 20,'Parking')
then query should return only 2 records as the costype(Parking),Rate(20),Billto(Customer)....its need to be done group by and 2 records should be get ...
1st record should be (2, 'Customer', 1, 1, 25,'Toll'), in the ID_list value=2
2nd record is sum of 1st and 3rd record and ID_list=1,3

That's what I thought. Did you look at the article I linked? It explains exactly how to do this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/