CODEWITHSUNDEEP
sqlsql-serverselect
abdol-hamid Hosseiny
abdol-hamid Hosseiny

Reputation: 132

multi rows in 1 rows , SQL server

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?

Upvotes: 1

Views: 45

Answers (2)

vCillusion
vCillusion

Reputation: 1809

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

Upvotes: 1

Dean Savović
Dean Savović

Reputation: 739

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

Upvotes: 2

Related Questions