CODEWITHSUNDEEP
sqlsql-server
user8512043
user8512043

Reputation: 1157

Multiple Sub-Queries In A SQL Query

I am creating a sample query that'll convert rows to column something as follows:

Person_Id   Total Earned Leave  Earned Leave Enjoyed    Remaining Earned Leave  Total Casual Leave  Casual Leave Enjoyed    Remaining Casual Leave
1001        20                  10                      10                      20                  4                       16

So above is the output I get and used multiple sub-queries using the following query:

SELECT DISTINCT m.Person_Id, (SELECT k.Leave_Allocation FROM LeaveDetails k WHERE k.Leave_Name = 'Earn Leave' 
AND k.Person_Id = 1001 AND k.[Year] = '2017') AS 'Total Earned Leave',

(SELECT o.Leave_Enjoy FROM LeaveDetails o WHERE o.Leave_Name = 'Earn Leave' 
AND o.Person_Id = 1001 AND o.[Year] = '2017') AS 'Earned Leave Enjoyed',

(SELECT p.Leave_Remain FROM LeaveDetails p WHERE p.Leave_Name = 'Earn Leave' 
AND p.Person_Id = 1001 AND p.[Year] = '2017') AS 'Remaining Earned Leave',

(SELECT k.Leave_Allocation FROM LeaveDetails k WHERE k.Leave_Name = 'Casual Leave' 
AND k.Person_Id = 1001 AND k.[Year] = '2017') AS 'Total Casual Leave',

(SELECT o.Leave_Enjoy FROM LeaveDetails o WHERE o.Leave_Name = 'Casual Leave' 
AND o.Person_Id = 1001 AND o.[Year] = '2017') AS 'Casual Leave Enjoyed',

(SELECT p.Leave_Remain FROM LeaveDetails p WHERE p.Leave_Name = 'Casual Leave' 
AND p.Person_Id = 1001 AND p.[Year] = '2017') AS 'Remaining Casual Leave'

FROM LeaveDetails m WHERE m.Person_Id = 1001 AND m.[Year] = '2017'

I am not sure if I am going to have performance issue here as there will be lots of data and was arguing if this will be better than Pivot or Run-Time Table Creation. I just want to make sure if this is going to be a better choice for the purpose I am trying to accomplish. You can share your ideas as well samples using SQL Server, MySQL or Oracle for better performance issue - Thanks.

Sample Table and Data:

CREATE TABLE [dbo].[LeaveDetails](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Person_Id] [nvarchar](20) NULL,
    [Leave_Name] [nvarchar](40) NULL,
    [Leave_Allocation] [float] NULL,
    [Leave_Enjoy] [float] NULL,
    [Leave_Remain] [float] NULL,
    [Details] [nvarchar](100) NULL,
    [Year] [nvarchar](10) NULL,
    [Status] [bit] NULL
)

INSERT [dbo].[LeaveDetails] ([Id], [Person_Id], [Leave_Name], [Leave_Allocation], [Leave_Enjoy], [Leave_Remain], [Details], [Year], [Status]) VALUES (1, N'1001', N'Earn Leave', 20, 10, 10, NULL, N'2017', 1)
INSERT [dbo].[LeaveDetails] ([Id], [Person_Id], [Leave_Name], [Leave_Allocation], [Leave_Enjoy], [Leave_Remain], [Details], [Year], [Status]) VALUES (2, N'1001', N'Casual Leave', 20, 4, 16, NULL, N'2017', 1)

Upvotes: 1

Views: 76

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

Use conditional aggregation:

SELECT m.Person_Id,
       MAX(CASE WHEN m.Leave_Name = 'Earn Leave' THEN k.Leave_Allocation END) as [Total Earned Leave],
       MAX(CASE WHEN m.Leave_Name = 'Earn Leave' THEN m.Leave_Enjoy END) as [Earned Leave Enjoyed],
       MAX(CASE WHEN m.Leave_Name = 'Earn Leave' THEN m.Leave_Remain END) as [Remaining Earned Leave],
       MAX(CASE WHEN m.Leave_Name = 'Casual Leave' THEN k.Leave_Allocation END) as [Total Casual Leave],
       MAX(CASE WHEN m.Leave_Name = 'Casual Leave' THEN k.Leave_Remain END) as [Casual Leave Enjoyed],
       MAX(CASE WHEN m.Leave_Name = 'Casual Leave' THEN k.Leave_Remain END) as [Remaining Casual Leave]
FROM LeaveDetails m
WHERE m.Person_Id = 1001 AND m.[Year] = '2017'
GROUP BY m.Person_ID;

Note: I do not advocate having special characters (such as spaces) in column aliases. If you do, use the proper escape character (square braces). Only use single quotes for string and date constants.

Upvotes: 3

jwolf
jwolf

Reputation: 938

PIVOT would work, but it looks like this is simply a single row that you want pivoted to a columnar output and the column names are known explicitly. If that's the case, you could just UNION the single column results together:

SELECT 'Person_ID' as col_name, Person_Id as col_value FROM LeaveDetails WHERE Person_Id = 1001 AND [Year] = '2017'
UNION
SELECT 'Leave_Enjoy' as col_name, Leave_Enjoy as col_value FROM LeaveDetails WHERE Person_Id = 1001 AND [Year] = '2017'
UNION
...

It's a lot simpler to write, cleaner to read, and should run a little faster - there is still one table scan for each column. Is the table indexed on Person_ID and Year?

If speed is an issue you could create a temp table of the one row:

SELECT * into #ld_temp FROM LeaveDetails WHERE Person_Id = 1001 AND [Year] = '2017'

then select from the temp table in the SELECT/UNION code:

SELECT 'Person_ID' as col_name, Person_Id as col_value FROM #ld_temp
UNION
SELECT 'Leave_Enjoy' as col_name, Leave_Enjoy as col_value FROM #ld_temp
UNION
...

Now you're down to just a single scan of the big table.

I hope this helps.

Upvotes: 2

Related Questions