Reputation: 8865
i have requirement where i have converted row values into columns but what i'm trying to remove null rows and show only data
declare @t table (id int,keys varchar(10),val varchar(10))
insert into @t (id,keys,val)values (1,'name','hulk'),(2,'age','22'),(3,'name','ironman'),(4,'age','35')
;with CTE AS (
Select [name],CAST([age] AS INT)age from (
select id,keys,val from @t )t
PIVOT(MAX(val) for KEYS in ([name],[age]))P
GROUP BY [name],age)
Select (C.name),(C.AGE) from CTE C
LEFT JOIN CTE cc ON c.age = cc.age AND c.name = cc.name
result :
name AGE
NULL 22
NULL 35
hulk NULL
ironman NULL
Desired result set :
name AGE
hulk 22
ironman 35
Upvotes: 0
Views: 1143
Reputation: 1271231
Your data structure is quite flawed, because it doesn't have a person identity.
If you assume that "name" begins each record, there is something you can do:
select max(case when keys = 'name' then val end) as name,
max(case when keys = 'age' then val end) as age
from (select t.*,
sum(case when keys = 'name' then 1 else 0 end) over (order by id) as numnames
from @t t
) t
group by numnames;
This very much relies on the fact that id
encodes the ordering of the records (as in your sample data). I would suggest that you include another identifier, if that is possible.
EDIT:
The equivalent query in pre-2012 versions of SQL Server:
select max(case when keys = 'name' then val end) as name,
max(case when keys = 'age' then val end) as age
from (select t.*, tt.numnames
from @t t cross apply
(select count(*) as numnames
from @t t2
where t2.id <= t.id and t2.keys = 'name'
) tt
) t
group by numnames;
Upvotes: 2
Reputation: 776
if I am guessing correct you want a pivot view on name and age and hence the unusual format of data. What you can do is relate the data, like name:hulk
and age:22
, and so on. So what you can do is:
declare @t table (id int,keys varchar(10),val varchar(10))
insert into @t (id,keys,val)values (1,'name','hulk'),(1,'age','22'),
(2,'name','ironman'),(2,'age','35');
with CTE AS (
Select [name],CAST([age] AS INT)age from (
select id,keys,val from @t )t
PIVOT(MAX(val) for KEYS in ([name],[age]))P
GROUP BY [name],age)
Select (C.name),(C.AGE) from CTE C
LEFT JOIN CTE cc ON c.age = cc.age AND c.name = cc.name
Once the relation is created, you will get the desired output. I created the relation by entering Id as 1 for name:hulk
with age:22
by saying that the data entered are for the same Id here Id:1
or could be any object you have defined in your application using this data.
Upvotes: 0
Reputation: 15125
I agree with Gordon, the data structure is flawed. If however, you can always assume the age key ID follows the name key ID by 1, the following will work. But if you can change the table structure, so much the better..
declare @t table (id int,keys varchar(10),val varchar(10))
insert into @t (id,keys,val)
values (1,'name','hulk'),(2,'age','22'),(3,'name','ironman'),(4,'age','35')
select nc.id,nc.val as Name,xx.age
from @t nc
join (select id,val as Age from @t where keys='age') xx on xx.id=nc.id+1
where keys='name'
However, the code relies on possibly a bad assumption about the keys...
Upvotes: 2
Reputation: 48
I believe that the original row-wise table design is flawed by lacking another person indentity. For example, a SSN, or any unique person identifier key, in addition to just name, and age, and the row id.
Upvotes: 0