CODEWITHSUNDEEP
sqlsql-servert-sqljoin
Explorer
Explorer

Reputation: 305

Want to get only one row and other rows blank rows while joining

I have One table called tableA : 

Col1            Col2           Col3
------------------------------------
A25             45              12
A25             45              12
A25             45              12
B26             10              20

another table called tableB :

Col1                     Col2
------------------------------
A25                       D1
A25                       D2
A25                       D3
B26                       D4

When I join these two tables in SQL(T-SQL) with tableA.Col1 = tableB.Col1 I only want to get the first row of the equal left table rows once and the others are blanks.

My joining query in my own words is :

Select
  tableA.*, -- IF THERE ARE NO DUPLICATES,
            -- BUT PUT BLANKS FOR SUCCESSIVE ROWS IF THERE ARE DUPLICATES
  tableB.Col2
from tableA JOIN tableB
where tableA.Col1 = tableB.Col1

In other words I want the result like this

Colm1       Colm2       Colm3          Colm4
-------------------------------------------- 
A25          45           12             D1   
                                         D2
                                         D3
B26          10          20              D4

Thanks for your help

Upvotes: 1

Views: 592

Answers (4)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Try this query:

SELECT  x.*, y.Col2, y.Col3
FROM
(
    SELECT  b.Col1, b.Col2, 
        ROW_NUMBER() OVER(PARTITION BY b.Col1 ORDER BY b.Col2) RowNum
    FROM    dbo.TableB b
) x
OUTER APPLY
(
    SELECT  TOP(1) a.Col2, a.Col3
    FROM    dbo.TableA a
    WHERE   x.RowNum = 1
    AND x.Col1 = a.Col1
) y

Upvotes: 0

Jayvee
Jayvee

Reputation: 10873

this should work:

SELECT 
    case when row_number() over(partition by a.col1 order by a.col1)=1 then a.col1 else ' ' end, 
    case when row_number() over(partition by a.col1 order by a.col1)=1 then convert(varchar,a.col2) else ' ' end, 
    case when row_number() over(partition by a.col1 order by a.col1)=1 then convert(varchar,a.col3) else ' ' end, 
    b.col2
FROM(select distinct col1, col2, col3 from tableA) a
INNER JOIN tableB b 
ON(a.col1=b.col1)

Upvotes: 0

Vasan
Vasan

Reputation: 375

Try this SQLFiddle:

SELECT
CASE WHEN B.Row = 1 THEN A.Col1 END A,
CASE WHEN B.Row = 1 THEN A.Col2 END B,
CASE WHEN B.Row = 1 THEN A.Col3 END C,
B.Col2 D

FROM
(SELECT  Col1,Col2,Col3
 FROM Table1
 Group By Col1,Col2,Col3
) AS A
INNER JOIN
(
SELECT Col1, Col2 ,
ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col1) AS ROW FROM Table2
) AS B
ON A.Col1 = B.Col1

Upvotes: 0

Mike
Mike

Reputation: 3831

It looks like you are working on a presentation layer. SQL Server does not excel at this, but it can be done. Be sure to use an Order By to ensure you have the correct row order. See this SQL Fiddle.

Select 
 Colm1 = Case When RowId = 1 Then TableA.Col1 Else '' End
  ,Colm2 = Case When RowId = 1 Then Convert(varchar, TableA.Col2) Else '' End
  ,Colm3 = Case When RowId = 1 Then Convert(varchar, TableA.Col3) Else '' End
  ,Colm4 = TableB.Col2
From TableA
  Join
    (
      Select Col1
        ,Col2
        ,RowId = Row_Number() Over (Partition By Col1 Order By Col2)
      From TableB
    ) TableB
    On TableA.Col1 = TableB.Col1
Group By   TableA.Col1
  ,Case When RowId = 1 Then TableA.Col1 Else '' End
  ,Case When RowId = 1 Then Convert(varchar, TableA.Col2) Else '' End
  ,Case When RowId = 1 Then Convert(varchar, TableA.Col3) Else '' End
  ,TableB.Col2
Order By TableA.Col1, TableB.Col2

Update: now shows blanks instead of nulls

Upvotes: 3

Related Questions