CODEWITHSUNDEEP
sqlsql-server
M.Mahdipour
M.Mahdipour

Reputation: 603

SQL Server Select Rows to Columns with count

Assume I have the following table in sql server:

CREATE TABLE [dbo].[Table1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](max) NOT NULL,
    [Value] [int] NOT NULL,
 CONSTRAINT [PK_Table1] PRIMARY KEY;

, and these values in the table:

ID  Title   Value
1     A     1
2     B     2
3     C     3
4     D     4
5     E     1
6     F     1
7     G     3

, and assume that each Title may repeat at max 3 times. I want to get an output from only those title which has repeated Value in this table, in the following format:

Titl1   Title2   Title3  RepeatCount
A      E     F     3
C     G     NULL     2

I have written a query to get repeated items, but don't know how to convert output to this format in a single query. This is the query to get repeated items:

SELECT DISTINCT t1.Title, t1.Value,
      (SELECT Count(Value) from Table1 where Table1.Value = t1.Value) as RepeatCount
FROM Table1 t1 INNER JOIN
     Table1 t2
     ON t1.Title <> t2.Title AND t1.Value = t2.Value
ORDER BY Value

Any idea?

Upvotes: 0

Views: 916

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

another way of doing same,

declare @t table(ID int,Title varchar(20),Value int)
insert into @t values
(1,'A',1)
,(2,'B',2)
,(3,'C',3)
,(4,'D',4)
,(5,'E',1)
,(6,'F',1)
,(7,'G',3)
;With CTE as
(
SELECT *
,ROW_NUMBER()over(partition by Value order by Value)rn 
from @t
)
,CTE1 AS
(
SELECT * 
from cte c
where exists(
select * from cte c1    
where c1.value=c.value and c1.rn>1
)
)


select 
        (select title from cte1 c1 where c.value=c1.value and  rn = 1)title1
       ,(select title from cte1 c1 where c.value=c1.value and  rn = 2) title2
      ,(select title from cte1 c1 where c.value=c1.value and  rn = 3) title3
      ,(select max(rn) from cte1 c1 where c.value=c1.value) RepeatCount
from cte1 c

where rn=1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

I see this as a conditional aggregation query:

select max(case when seqnum = 1 then title end) as title1,
       max(case when seqnum = 2 then title end) as title2,
       max(case when seqnum = 3 then title end) as title3,
       count(*) as repeatcount
from (select t.*,
             row_number() over (partition by value order by id) as seqnum
      from table1 t
     ) t
group by value
having count(*) > 1;

Upvotes: 1

Related Questions