Add values from multiple rows in variable

Question

Hello

I’ve got a query that begins SELECT TOP 1 WITH TIES … that returns the following result

Rate    Period
50  P2
50  P8

What’s the best way to construct a string that reads ‘Periods P2 and P8 have the highest rate of 50’ . I want to avoid cursors as the SQL is built dynamically.

Many thanks
Ron

Answer 1

Generally speaking, it is better to do complex formatting in the front end, not in SQL. But you could do
Declare @Sample Table(Rate int, Period char(2));
Insert @Sample(Rate, Period) Values
(50, 'P2'),
(40, 'P4'),
(50, 'P8')

;With cte As
(Select Top 1 With Ties Rate, Period
From @Sample
Order By Rate Desc)
Select Distinct 
  (Select 'Periods' + Stuff(
    (Select ' And ' + c2.Period From cte c2 Order By c2.Period
    For XML Path(''),Type)
      .value('text()[1]','varchar(max)'),1,4,''))
  + ' have the highest rate of ' + Cast(c1.rate As varchar(11)) As MyResult
From cte c1;

Tom

Answer 2

Check if this example can be adjusted:

declare @table as table (Rate int, Period varchar(max))

insert @table values
( 10, 'P7' ),
( 50, 'P2' ),
( 20, 'P1' ),
( 50, 'P8' )

;
with Q1 as
(
    select top(1) with ties Period
    from @table 
    order by Rate desc
),
Q2 as 
(
    select COUNT(*) as cnt from Q1
),
Q3 as
(
    select cnt, 
        (select string_agg(Period, ', ') within group (order by Period) from (select top(iif(cnt = 0, 0, cnt - 1)) Period from Q1 order by Period) d) as s1, 
        (select top(1) Period from Q1 order by Period desc) as s2
    from Q2
)
select case cnt
    when 0 then 'No periods'
    when 1 then concat( 'Period ', s2, ' has the highest rate of 50')
    else concat( 'Periods ', s1, ' and ', s2, ' have the highest rate of 50')
    end
from Q3

Answer 3

One more solution.

SQL

-- DDL and sample data population, start
DECLARE @Sample TABLE (Rate INT, Period CHAR(2));
INSERT @Sample (Rate, Period) VALUES
(50, 'P2'),
(40, 'P4'),
(50, 'P8');
-- DDL and sample data population, end

;WITH rs (xmldata) AS
(
    SELECT TOP(1) WITH TIES
        Rate,
        Period
    FROM @Sample
    ORDER BY Rate DESC
    FOR XML PATH('r'), TYPE, ROOT('root')
)
SELECT 'Periods ' + 
    REPLACE(xmldata.query('data(/root/r/Period)').value('.','VARCHAR(30)')
        , SPACE(1), ' and ') +
    ' have the highest rate of ' + xmldata.value('(/root/r/Rate/text())[1]','VARCHAR(30)')
FROM rs;

Answer 4

Perfect answers.

Many thanks.