CODEWITHSUNDEEP
sqlsql-server
mohan111
mohan111

Reputation: 8865

how can i remove the null values in two columns to show only data

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

Answers (4)

Gordon Linoff
Gordon Linoff

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

debatanu
debatanu

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

Sparky
Sparky

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

Henry L
Henry L

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

Related Questions