How to write query for display data in SQL.

Nirav Daraniya

10y1.4k

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.

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Arjun Singh10y ago
give all possible scenario of record in table and output
0
Nirav Daraniya10y ago
if i add one more row like below
then youe query query (
Select B.SubId,B.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.)
0
Arjun Singh10y ago
You don't need pivot for this need,easy with this
Select B.SubId,B.Basic,(Select B1.Basic From Basics B1 Where B1.SubId=B.Id And B1.MetaId=2)
From Basics B Where MetaId=1
0
Arjun Singh10y ago
See this can help you
Select B.SubId,B.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
0
Nirav Daraniya10y ago
"" 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 SubId,MetaId,[BASIC]
FROM tbl_DemoTable
) AS source
PIVOT
(
MAX([BASIC])
FOR MetaId IN ([1], [2])
) as pvt
-------------------------------------------------------------
0
Arjun Singh10y ago
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
0
Arjun Singh10y ago
how last data should come in output?
0
Arjun Singh10y ago
so for B6 are you expecting NULL in Premium?
0
Nirav Daraniya10y ago
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.
0
Rahul Verma10y ago
select subid, bacis, premium from table_name where not NULL;
or
select subid, bacis, premium from table_name;
0
Arjun Singh10y ago
Try with this
Select B.SubId,B.Basic,B1.Basic As Premium From Basics B
Inner Join Basics B1 On B.Id=B1.SubId And B.MetaId!=B1.MetaId
0