multi rows in 1 rows , SQL server

Question

The problem is that if too many records to be entered

           |    id   |    name   |    age    |   Tel
------------------------------------------
      1    |    1   |   Frank   |    40     |   null
      2    |    1   |   null    |    50     |   7834xx
      3    |    1   |   Alex    |    null   |   null
      4    |    1   |   null    |    20     |   null
      5    |    2   |   James   |    null   |   4121xx

My Query :

 select id, max(name) as name, max(age) as age, max(tel) as tel
 from Table group by id;

Result = return the Maximum value Like:

           |    id   |   name    |    age    |   Tel
------------------------------------------
      1    |    1    |   Frank   |    50     |  7834xx

But I need Select Query like this: Ex: (maybe): select id, lastRowsNotNull(name) as name, lastRowsNotNull(age) as age ,lastRowsNotNull(tel) as tel from Table group by id;

           |    id   |   name    |    age    |   Tel
------------------------------------------
      1    |    1    |   Alex    |    20     |  7834xx

What do I do? pleas?

what is the first column? before that id? Do you have that serial number column? — Kannan Kandasamy
– Kannan Kandasamy, Commented Jun 7, 2017 at 19:33
How is 20 the max number in the age column? The results you are looking for don't seem to match the data. Is there another requirement? — Jacob H
– Jacob H, Commented Jun 7, 2017 at 19:34
You are not being very clear with the requirements. The query you have gives the data it should. What is the logic that would make age 20 > 50? — Jacob H
– Jacob H, Commented Jun 7, 2017 at 19:44
What makes you combine name='Alex' with age=20? Why not Frank with 20? Or Alex with 40 or 50? Or null with null? What is the rule? Do you simply want min(name), min(age), min(tel)? — Thorsten Kettner
– Thorsten Kettner, Commented Jun 7, 2017 at 20:35

Dean Savović · Accepted Answer · 2017-06-07 19:50:03Z

2

drop table if exists dbo.TableC;

create table dbo.TableC (
Ident int primary key
, Id int
, name varchar(100)
, age int
, Tel varchar(100)
);

insert into dbo.TableC (Ident, Id, name, age, Tel)
values (1, 1, 'Frank', 40, null)
, (2, 1, null, 50, '7834xx')
, (3, 1, 'Alex', null, null)
, (4, 1, null, 20, null)
, (5, 2, 'James', null, '4121xx');

select
*
from (
select
    MIN(t.Ident) as Ident
    , t.id
from dbo.TableC t
group by t.Id
) t
outer apply (
    select
        top (1)
        tn.name
    from dbo.TableC tn
    where tn.name is not null
        and tn.Id = t.Id
    order by tn.Ident desc
) tname
outer apply (
    select
        top (1)
        ta.age
    from dbo.TableC ta
    where ta.age is not null
        and ta.Id = t.Id
    order by ta.Ident desc
) tage
outer apply (
    select
        top (1)
        tt.tel
    from dbo.TableC tt
    where tt.Tel is not null
        and tt.Id = t.Id
    order by tt.Ident desc
) ttel

answered Jun 7, 2017 at 19:50

Dean Savović

7393 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

abdol-hamid Hosseiny Over a year ago

Thanks , its too Complicated !! I think there are another Easy Way , maybe : select id, lastRows!null(name) as name, ... from Table group by id; i need last is not Null

vCillusion Over a year ago

check out my answer using cross apply on the query.

vCillusion · Accepted Answer · 2018-06-07 01:04:16Z

I tried to simplify the logic by introducing the Cross Apply and selecting the top column values Demo

Main Logic

SELECT id, name, age, Tel FROM 
(SELECT MIN(rn) AS rn, id
 FROM 
 #RecordsTable
 GROUP BY id
) A
CROSS APPLY(    
SELECT 
     (SELECT TOP 1 name FROM #RecordsTable WHERE name IS NOT NULL AND id = A.id Order by rn DESC) name,
     (SELECT TOP 1 age FROM #RecordsTable WHERE age IS NOT NULL AND id = A.id Order by rn DESC) age,
     (SELECT TOP 1 Tel FROM #RecordsTable WHERE Tel IS NOT NULL AND id = A.id Order by rn DESC) Tel    
) B;

Full Query

IF OBJECT_ID('tempdb..#RecordsTable') IS NULL
BEGIN
  CREATE TABLE tempdb..#RecordsTable(rn INT IDENTITY(1, 1) NOT NULL, id INT, name VARCHAR(MAX), age INT, Tel VARCHAR(MAX));
END

;WITH RecordsTable AS(
  SELECT 1 AS id, 'Frank' AS name, 40 AS age, NULL AS Tel UNION ALL
  SELECT 1 , NULL, 50, '7834xx' UNION ALL
  SELECT 1, 'Alex', null, null UNION ALL
  SELECT 1, null, 20, null UNION ALl
  SELECT 2, 'James', null, '4121xxx'
)
INSERT INTO #RecordsTable
SELECT id, name, age, Tel FROM RecordsTable;

SELECT id, name, age, Tel FROM 
(SELECT MIN(rn) AS rn, id
 FROM 
 #RecordsTable
 GROUP BY id
) A
CROSS APPLY( 

SELECT 
     (SELECT TOP 1 name FROM #RecordsTable WHERE name IS NOT NULL AND id = A.id Order by rn DESC) name,
     (SELECT TOP 1 age FROM #RecordsTable WHERE age IS NOT NULL AND id = A.id Order by rn DESC) age,
     (SELECT TOP 1 Tel FROM #RecordsTable WHERE Tel IS NOT NULL AND id = A.id Order by rn DESC) Tel

) B;

DROP TABLE #RecordsTable

Output

1   Alex    20  7834xx
2   James   NULL    4121xxx

Collectives™ on Stack Overflow

multi rows in 1 rows , SQL server

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related