April 6, 2023 at 4:12 am
hi,
I have used two ways to get the value of the col in a variable.
Which one I should prefer? In @strcol1 I need all top 100 coma seprated values and in @intcol1 I need the 100th value.
Other ways are also there, but I want from following answer.
--1)
DECLARE @strcol1 varchar(max), @intco11 int
SELECT @strcol1 += ','+CAST(col1 AS VARCHAR(20))
,@intco11=col1 FROM
(SELECT TOP 100 col1
FROM xyz
ORDER BY col1)
ORDER BY col1
--2)
DECLARE @strcol1 varchar(max), @intco11 int
SELECT TOP 100 @strcol1 += ','+CAST(col1 AS VARCHAR(20))
,@intco11=col1
FROM xyz
ORDER BY col1
April 6, 2023 at 9:33 am
April 6, 2023 at 4:56 pm
If it were only the 100th row maybe OFFSET paging. Since it's both the aggregation of the rows as well as the 100th value you could try using ROW_NUMBER in a CTE. Then aggregate the strings using STRING_AGG and select the 100th value using conditional aggregation (which is similar to PIVOT)
declare
@strcol1 varchar(max),
@intco11 int;
with xyz_cte as (
select top(100) *, row_number() over (order by col1) rn
from xyz)
select @strcol1=string_agg(col1, ',') within group (order by col1),
@intco11=max(iif(rn=100, col1, null))
from xyz_cte;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 6, 2023 at 5:59 pm
I've found that most people that want to know how to do this are solving a problem the wrong way. What is the end use of this concatenated string and what will it be used by?
This is kind of like people saying that "I need to iterate over the rows in a table..." and it turns out that nothing even close is necessary. Sometimes it actually is but most times it's because they simply don't know differently.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2023 at 7:34 am
I agree with your point, but it is needed sometimes, so please tell me is there anything wrong in first query or should I go for second one or use some with cte etc. etc.
Yours sincerely
April 11, 2023 at 7:35 am
please tell me is there anything wrong in first query or should I go for second one or use some with cte etc. etc.
Yours sincerely
April 11, 2023 at 10:11 am
please tell me is there anything wrong in first query or should I go for second one or use some with cte etc. etc.
The method you have used in your OP is actually a documented antipattern so yes you should change it; there is no guarantee you will get the desired behaviour due a reliance of how SQL Server will process the rows (in a row by row order). STRING_AGG is the way to go.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 11, 2023 at 10:39 am
insted of first query i wanted to known about second query. so i am rephrasing the question.
here i want the activity name and code of first activiytid?
they did not write about top n , i checked with-out top n and with order by it is undeterminstic. but with top n and order by it is working fine like this one gets the correct record
declare @v-2 varchar(max)='',@v1 varchar(max)=''
select top 1 @v-2=activityname ,@v1= code from activity order by activityid
select @v-2
but this one also works select @v-2=activityname from activity order by activityid desc
select @v-2
so i my first query correct.
April 11, 2023 at 10:52 am
select @v=activityname from activity order by activityid
That will assign every value of activityname to the variable @v, but as the variable is a scalar value, it will be overwritten every time. This results in the final value of @v being the value of activityname for the highest value of activityid, as that is the last value to be assigned to the variable.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 11, 2023 at 11:01 am
write , my question is with top n would it work?
here i want the activity name and code of first activiytid? ( activitid is pk)
declare @v varchar(max)='',@v1 varchar(max)=''
select top 1 @v=activityname ,@v1= code from activity order by activityid
select @v-2,@v1
2)
i have sql server 2012 then how to write this in old syntex
DECLARE @List AS nvarchar(max);
SELECT @List = STRING_AGG(p.LastName,', ') WITHIN GROUP (ORDER BY p.BusinessEntityID) FROM Person.Person AS p WHERE p.FirstName = 'William'; SELECT @List;
April 11, 2023 at 3:10 pm
I agree with your point, but it is needed sometimes, so please tell me is there anything wrong in first query or should I go for second one or use some with cte etc. etc.
Yours sincerely
NP. Use one of the String_Agg solutions.
The fact that you're outputting that result to a variable is what I and the others are concerned about, especially from a performance aspect. It's like people that start posts with "I need to iterate over rows to..." or "How can I loop through the rows to...".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2023 at 4:40 pm
i have sql server 2012 then how to write this in old syntex
You've posted in the SQL Server 2022 forum, so we've been running under the (incorrect) knowledge that that is the version you are running. For old (unsupported) versions of SQL Server, you have to use the "old" method of FOR XML PATH and (STUFF) to get the desired results.
In very simple terms:
SELECT STUFF((SELECT CONCAT(N',', V.YourColumn)
FROM (VALUES(1,'def'),
(2,'abc'),
(3,'xyz'))V(YourID,YourColumn)
ORDER BY YourID
FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,'');
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 11, 2023 at 6:58 pm
As the OP want an opinion about the two code options presented, i.e.
Option 1:
DECLARE @strcol1 varchar(max), @intco11 int
SELECT @strcol1 += ','+CAST(col1 AS VARCHAR(20)), @intco11=col1
FROM (
SELECT TOP 100 col1
FROM xyz
ORDER BY col1
)
ORDER BY col1
Option 2:
DECLARE @strcol1 varchar(max), @intco11 int
SELECT TOP 100 @strcol1 += ','+CAST(col1 AS VARCHAR(20)), @intco11=col1
FROM xyz
ORDER BY col1
Personally I can't see any advantage to Option 1, so I would definitely go for Option 2.
Just note that you need to initialize @strcol1 to an empty string (the DECLARE initializes the variable to null) and that you need to remove the resulting comma prefix - or handle it in a different way, eg. with an IIF or CASE WHEN in the select. As is your code will not work as intended.
April 11, 2023 at 8:10 pm
As the OP want an opinion about the two code options presented, i.e.
Option 1:
DECLARE @strcol1 varchar(max), @intco11 int
SELECT @strcol1 += ','+CAST(col1 AS VARCHAR(20)), @intco11=col1
FROM (
SELECT TOP 100 col1
FROM xyz
ORDER BY col1
)
ORDER BY col1Option 2:
DECLARE @strcol1 varchar(max), @intco11 int
SELECT TOP 100 @strcol1 += ','+CAST(col1 AS VARCHAR(20)), @intco11=col1
FROM xyz
ORDER BY col1Personally I can't see any advantage to Option 1, so I would definitely go for Option 2.
Just note that you need to initialize @strcol1 to an empty string (the DECLARE initializes the variable to null) and that you need to remove the resulting comma prefix - or handle it in a different way, eg. with an IIF or CASE WHEN in the select. As is your code will not work as intended.
In both pieces of code, you have to account for the initial value of @strcol1 being NULL because, as you know, NULL + Anything = NULL. You can't just change the default, either, because that would leave you with a leading comma.
So... a minor tweak to your good code will take care of both of those issues...
Option 1:
DECLARE @strcol1 varchar(max), @intco11 int
;
SELECT @strcol1 = ISNULL(@strcol1+',','')+CAST(t.N AS VARCHAR(20)), @intco11=col1
FROM (
SELECT TOP 100 col1
FROM xyz
ORDER BY col1
)
ORDER BY col1
;
Option 2:
DECLARE @strcol1 varchar(max), @intco11 int
;
SELECT TOP 100 @strcol1 = ISNULL(@strcol1+',','')+CAST(t.N AS VARCHAR(20)), @intco11=col1
FROM xyz
ORDER BY col1
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply