CODEWITHSUNDEEP
sqlsql-servergroup-by
RAJ
RAJ

Reputation: 57

SQL Query for grouping the data

I have a data set some thing like below.

Code          Values
521            1
522            1
523            1
524            0
525            0
526            1
527            1

I am expecting a output something like below:

Group     CD        Values
G1      521-523       1
G2      524-525       0
G3      526-527       1

Upvotes: 3

Views: 79

Answers (3)

Deep
Deep

Reputation: 3202

Try this solution :

DECLARE @YourTable TABLE (Code INT, Value INT)

INSERT INTO @YourTable
VALUES
(521,1),
(522,1),
(523,1),
(524,0),
(525,0),
(526,1),
(527,1)

;WITH CTE AS
(
    SELECT  *, ROW_NUMBER() OVER(ORDER BY CODE) - ROW_NUMBER() OVER(PARTITION BY Value Order BY CODE) Grp
    FROM    @YourTable
)
SELECT  ROW_NUMBER() OVER(ORDER BY(SELECT MIN(Code))) Grp
        ,CAST(MIN(Code) AS VARCHAR(30)) +'-'+ CAST(MAX(Code) AS VARCHAR(30)) CD
        ,MAX(Value) Val
FROm    CTE
GROUP   BY Grp

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I would be inclined to use the difference of row numbers approach:

select concat('G', row_number() over (order by min(value)) as grp,
       concat(min(code), '-', max(code)) as codes,
       value
from (select t.*,
             row_number() over (order by code) as seqnum_c,
             row_number() over (partition by value order by code) as seqnum_vc
      from t
     ) t
group by value, (seqnum_c - seqnum_vc);

Upvotes: 2

sagi
sagi

Reputation: 40481

Try using LEAD() , ROW_NUMBER() , and conditonal aggregation :

SELECT 'G' + ROUND((p.rnk + 1) / 2) as group,
       MAX(CASE WHEN p.rnk%2 = 1 THEN p.code END) + '-' +
       MAX(CASE WHEN p.rnk%2 = 0 THEN p.code END) 
FROM (
    SELECT s.*,
           ROW_NUMBER() OVER(ORDER BY s.code) as rnk
    FROM (
        select t.*,
               LEAD(t.values,1) OVER(ORDER BY t.code DESC) as last_val
        FROM YourTable t) s
    WHERE s.values <> s.last_val ) p
GROUP BY 'G' + ROUND((p.rnk + 1) / 2)

Upvotes: 1

Related Questions