asked on

Get the data in a different format - maybe pivot?

This is SQL 2014

I have this sample data

create table #temp
(
   rowId int identity,
   cId int,
   year int,
   rank int,
   cName varchar(500)
)
 
 
 
insert into #temp
select 1, 1712, 2015,3,'test1'
 
insert into #temp
select 2, 1929, 2015,2302,'test2'
 
insert into #temp
select 3, 1712, 2014,1,'test1'
 
insert into #temp
select 3, 1929, 2014,2024,'test2'

Open in new window

I get 4 row. This is how I want it to look like. (I wanted to loop thru the #temp table, compare the "cid" and then maybe concat and dump the result into another table but that can't be the correct way of doing this)

I need the data in "Rank" column to be from earliest year (2014) to latest year (2015)

CId Year Rank Cname

1712 2014,2015 1,3 test1

1929 2014, 2015 2024,2302 test2

PortletPaul

"for xml path" and stuff() can be used for this I believe, something like this

SELECT
        t.Cid
      , t.cname
      , ca1.years
      , ca2.ranks
FROM [YourTable] t
    CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ', ' + CAST(t2.year AS varchar(20))
               FROM [YourTable] AS t2
              WHERE t.Cid = t2.Cid and t.cname = t2.cname
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca1 (Years)
    CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ', ' + CAST(t2.rank AS varchar(20))
               FROM [YourTable] AS t3
              WHERE t.Cid = t3.Cid and t.cname = t3.cname
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca2 (Ranks)
GROUP BY
  t.Cid
, t.cname
;

Open in new window

Camillia

ASKER

thanks. Let me try

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

Thank. I'll try today.