CODEWITHSUNDEEP
sql-serverjoin
Anoop
Anoop

Reputation: 11

SQL Query to join the same table based on condition

Can anyone please help me write a query to get the below results.

Table goes like this: 

    Table1
    ------------
    Col1    Col2
    ===     ====
    A       Error
    B       Success
    C       Success
    D       Success

This is the result of the query (after using Join statement):

     Success    Error
     --------   ------
     B          A
     C          A
     D          A

Expected result:

     Success    Error
     B          A
     C          0
     D          0

The values in Col2 can vary - like mentioned below. 

    Col1    Col2
    ----    -----
    A       Error
    B       Success
    C       Success
    D       Failed
    E       Unknown
    F       Success
    G       Error

And the expected result:

    Success Failed  Error   Unknown
    ------- ------  -----   -------
    B       D       A       E   
    C       0       G       0
    F       0       0       0

I'm trying to get the values in 2nd column as headers in the new table and the values in 1st column as row values in the new table.

Here is my SQL code:

Select tb1.Col1 as Success, tb2.Col1 as Error 
from ( Select * from table1 where Col2 = 'Success') tb1 
Join ( Select * from table1 where Col2 = 'Error') tb2 
on tb1.Col1 is not NUll

Upvotes: 1

Views: 2727

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

It is standart pivot task:

Create table t(code char(1), status nvarchar(10))

Insert into t values
( 'A',       'Error'),
(  'B',       'Success'),
(  'C',       'Success'),
(  'D',       'Failed'),
( 'E',       'Unknown'),
(   'F',       'Success'),
( 'G',       'Error')

;with cte as (Select *, row_number() over(partition by status order by code) c from t)
 Select success, failed, error, unknown from cte
 Pivot(max(code) for status in([error],[failed],[unknown],[success])) p

Fiddle: http://sqlfiddle.com/#!6/06e8c/12

Upvotes: 0

shA.t
shA.t

Reputation: 16958

I think @JamesZ's answer is correct, But if OP needs that result with a dynamic values for Col2 I suggest this code:

Declare @query nvarchar(max) = ''

select @query = @query + ', isnull(max(case when col2 = ''' + Col2 + ''' then col1 else null end), 0) as ' + Col2
from (select distinct Col2 from temptable) dt

set @query = 'select ' + substring(@query, 2, len(@query)) +
   ' from (select col2, col1, row_number() over (partition by col2 order by col1) as rn from temptable) TMP group by RN'

exec(@query)

The columns of result now is pending to variety of Col2:

Error | Failed | Success | Unknown
------+--------+---------+-------------
A     | D      | B       | E
G     | 0      | C       | 0
0     | 0      | F       | 0

Upvotes: 0

James Z
James Z

Reputation: 12317

You just want to get the list of the items next to each other without any actual relation between them? Something like this with row_number should work:

select
  max(case when col2 = 'Success' then col1 else null end) as Success,
  max(case when col2 = 'Failed' then col1 else null end) as Failed,
  max(case when col2 = 'Error' then col1 else null end) as Error,
  max(case when col2 = 'Unknown' then col1 else null end) as Unknown
from (
select
  col2,
  col1,
  row_number() over (partition by col2 order by col1) as rn
from
  data
) TMP
group by RN

SQL Fiddle: http://sqlfiddle.com/#!6/8c641/4

Upvotes: 2

Related Questions