how to add multiple same column header based on data sql

BASKAR_DHANHIND_LAKSHIMI 61 Reputation points
2021-10-08T06:42:05.35+00:00

I have show expense report as per attached image
138831-outp.jpg
please find the table script
----start

/****** Object: Table [dbo].[Emp_expdetails] Script Date: 08-10-2021 11:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Emp_expdetails](
[Tripid] [int] NULL,
[expid] [int] IDENTITY(1,1) NOT NULL,
[Empid] [int] NULL,
[Exphead] varchar NULL,
[mode] varchar NULL,
[Explocation] varchar NULL,
[Remark] varchar NULL,
[Entereddate] [datetime] NULL,
[Expamount] [money] NULL,
[Status] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Emp_expmaster] Script Date: 08-10-2021 11:57:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Emp_expmaster](
[Exphead] varchar NULL,
[Status] [int] NULL,
[Enabledate] [datetime] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Emp_expdetails] ON
GO
INSERT [dbo].[Emp_expdetails] ([Tripid], [expid], [Empid], [Exphead], [mode], [Explocation], [Remark], [Entereddate], [Expamount], [Status]) VALUES (1002, 5, 11, N'Travel', N'bus', N'//fgt.gif', N'', CAST(N'2021-10-06T15:46:17.243' AS DateTime), 1500.0000, 0)
GO
INSERT [dbo].[Emp_expdetails] ([Tripid], [expid], [Empid], [Exphead], [mode], [Explocation], [Remark], [Entereddate], [Expamount], [Status]) VALUES (1002, 6, 11, N'conveyance', N'', N'//fgt.gif', N'conve', CAST(N'2021-10-06T15:46:17.260' AS DateTime), 1200.0000, 0)
GO
INSERT [dbo].[Emp_expdetails] ([Tripid], [expid], [Empid], [Exphead], [mode], [Explocation], [Remark], [Entereddate], [Expamount], [Status]) VALUES (1003, 7, 11, N'Travel', N'bus', N'//fgt.gif', N'', CAST(N'2021-10-06T15:46:49.733' AS DateTime), 1600.0000, 0)
GO
INSERT [dbo].[Emp_expdetails] ([Tripid], [expid], [Empid], [Exphead], [mode], [Explocation], [Remark], [Entereddate], [Expamount], [Status]) VALUES (1003, 8, 11, N'conveyance', N'', N'//fgt.gif', N'conve', CAST(N'2021-10-06T15:46:49.733' AS DateTime), 1200.0000, 0)
GO
INSERT [dbo].[Emp_expdetails] ([Tripid], [expid], [Empid], [Exphead], [mode], [Explocation], [Remark], [Entereddate], [Expamount], [Status]) VALUES (1004, 11, 11, N'Food', N'saravana bhavan', N'//gif.jpg', N'for food', CAST(N'2021-10-07T12:09:05.563' AS DateTime), 700.0000, 1)
GO
INSERT [dbo].[Emp_expdetails] ([Tripid], [expid], [Empid], [Exphead], [mode], [Explocation], [Remark], [Entereddate], [Expamount], [Status]) VALUES (1004, 12, 11, N'Conveyance', N'conveyance', N'//gif.jpg', N'for travel', CAST(N'2021-10-07T12:09:05.563' AS DateTime), 100.0000, 0)
GO
INSERT [dbo].[Emp_expdetails] ([Tripid], [expid], [Empid], [Exphead], [mode], [Explocation], [Remark], [Entereddate], [Expamount], [Status]) VALUES (1003, 9, 11, N'Conveyance', N'internet', N'//fgt.gif', N'', CAST(N'2021-10-06T16:57:41.020' AS DateTime), 100.0000, 0)
GO
INSERT [dbo].[Emp_expdetails] ([Tripid], [expid], [Empid], [Exphead], [mode], [Explocation], [Remark], [Entereddate], [Expamount], [Status]) VALUES (1003, 10, 11, N'salespromo', N'', N'//fgt.gif', N'conve', CAST(N'2021-10-06T16:57:41.020' AS DateTime), 100.0000, 0)
GO
SET IDENTITY_INSERT [dbo].[Emp_expdetails] OFF
GO
INSERT [dbo].[Emp_expmaster] ([Exphead], [Status], [Enabledate]) VALUES (N'Conveyance', 1, CAST(N'2021-06-10T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Emp_expmaster] ([Exphead], [Status], [Enabledate]) VALUES (N'Food', 1, CAST(N'2021-06-10T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Emp_expmaster] ([Exphead], [Status], [Enabledate]) VALUES (N'Communication', 1, CAST(N'2021-06-10T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Emp_expmaster] ([Exphead], [Status], [Enabledate]) VALUES (N'Salespromo', 1, CAST(N'2021-06-10T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[Emp_expmaster] ([Exphead], [Status], [Enabledate]) VALUES (N'Travel', 1, CAST(N'2021-06-10T00:00:00.000' AS DateTime))
GO

----end

![138801-image.png]3

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,705 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,799 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-10-08T09:12:42.863+00:00

    I suggest you try the following format: 

    select ID,emp_Code,emp_Name  
,tr.Tripid,Fromplace,Toplace  
,convert(varchar,Fromdate,103) fromdate  
,convert(varchar,todate,103) todate  
,noofdays,Totalamount,ismetro,tr.Remarks  
,e.[Exphead],e.[mode],e.[Expamount]  
,e.[Status]  
from Emp_traveldetails tr  
left join EMPYTBL sm on tr.Empid=sm.ID  
JOIN [dbo].[Emp_expdetails] e ON tr.[Tripid]=e.[Tripid]

    Output:
    138787-image.png

    According to your description, if there are multiple [Exphead], [mode], [Expamount], [Status] corresponding to one [Tripid], it will be troublesome and unnecessary to set up columns for each one separately.

    If you have any question, please feel free to let me know.

    Regards
    Echo

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.