How to write query for display data in SQL.

Question

I have table in MS SQL as show below. I want the Output like as show below image without using any temporary table in SQL server.so please help me.

Arjun Singh · Answer

give all possible scenario of record in table and output

Nirav Daraniya · Answer

if i add one more row like below then youe query query ( Select B.SubIdB.Basic(Select B1.Basic From Basics B1 Where B1.SubId=B.Id And B1.MetaId=2) From Basics B Where MetaId=1 ) give wrong output like (here ID is primary key  so once i enter one row and then delete it and then insert another time then this situation is generate.)

Arjun Singh · Answer

You don't need pivot for this needeasy with this Select B.SubIdB.Basic(Select B1.Basic From Basics B1 Where B1.SubId=B.Id And B1.MetaId=2) From Basics B Where MetaId=1

Arjun Singh · Answer

See this can help you Select B.SubIdB.Basic Case When (Select B1.Basic From Basics B1 Where B1.Id=B.SubId And B1.MetaId=2) Is Not Null Then (Select B1.Basic From Basics B1 Where B1.Id=B.SubId And B1.MetaId=2) When (Select B1.Basic From Basics B1 Where B1.Id=B.SubId And B1.MetaId=2) Is Null Then (Select B1.Basic From Basics B1 Where B1.SubId=B.Id And B1.MetaId=2) End From Basics B Where MetaId=1

Nirav Daraniya · Answer

so for B6 are you expecting NULL in Premium?  Yes i want it to be null but now i get solution. as below ------------------------------------------------------------- SELECT * FROM ( SELECT SubIdMetaId[BASIC] FROM tbl_DemoTable ) AS source PIVOT ( MAX([BASIC]) FOR MetaId IN ([1] [2]) ) as pvt -------------------------------------------------------------

Arjun Singh · Answer

The problem is here you have cross reference linking with basic and premium 1- Premium is linked with basic for record 1-10 2- Basic is linked with Premium for record 11

Arjun Singh · Answer

how last data should come in output?

Arjun Singh · Answer

so for B6 are you expecting NULL in Premium?

Nirav Daraniya · Answer

Hi Arjun Singh You are right for the above data as show in image  but here my data isfix it can be change.so if i add one more row to my table as show in figure . Now if i run your query then its give me wrong output.

Rahul Verma · Answer

select subid bacis premium from table_name where not NULL; or select subid bacis premium from table_name;

Arjun Singh · Answer

Try with this Select B.SubIdB.BasicB1.Basic As Premium From Basics B Inner Join Basics B1 On B.Id=B1.SubId And B.MetaId!=B1.MetaId