tbaseflug
asked on
Insert into table - into columns based upon where clause in select from
I have the below table - it has three seperate column "sets"/"groups" where I want to take my select code sample and where row = 1 insert into 1st column set, where row = 2 insert into 2nd. etc.
CREATE TABLE [dbo].[tblPROFEE_RVU_Mods] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[HCPCS] [varchar](20) NULL,
[carrierLoc] [varchar](20) NULL,
------ First col set
[1mod] [varchar](20) NULL,
[1facRVU] [decimal](5, 2) NULL,
[1nonFacRVU] [decimal](5, 2) NULL,
[1mpRVU] [decimal](5, 2) NULL,
[1fac] money NULL,
[1nonFac] money NULL,
------ second col set
[2mod] [varchar](20) NULL,
[2facRVU] [decimal](5, 2) NULL,
[2nonFacRVU] [decimal](5, 2) NULL,
[2mpRVU] [decimal](5, 2) NULL,
[2fac] money NULL,
[2nonFac] money NULL,
------ third col set
[3mod] [varchar](20) NULL,
[3facRVU] [decimal](5, 2) NULL,
[3nonFacRVU] [decimal](5, 2) NULL,
[3mpRVU] [decimal](5, 2) NULL,
[3fac] money NULL,
[3nonFac] money NULL,
CREATE TABLE [dbo].[tblPROFEE_RVU_Mods]
[ID] [int] IDENTITY(1,1) NOT NULL,
[HCPCS] [varchar](20) NULL,
[carrierLoc] [varchar](20) NULL,
------ First col set
[1mod] [varchar](20) NULL,
[1facRVU] [decimal](5, 2) NULL,
[1nonFacRVU] [decimal](5, 2) NULL,
[1mpRVU] [decimal](5, 2) NULL,
[1fac] money NULL,
[1nonFac] money NULL,
------ second col set
[2mod] [varchar](20) NULL,
[2facRVU] [decimal](5, 2) NULL,
[2nonFacRVU] [decimal](5, 2) NULL,
[2mpRVU] [decimal](5, 2) NULL,
[2fac] money NULL,
[2nonFac] money NULL,
------ third col set
[3mod] [varchar](20) NULL,
[3facRVU] [decimal](5, 2) NULL,
[3nonFacRVU] [decimal](5, 2) NULL,
[3mpRVU] [decimal](5, 2) NULL,
[3fac] money NULL,
[3nonFac] money NULL,
SELECT DISTINCT c.hcpcs, c.CarrierLoc,
CASE WHEN r.MOD IS NULL THEN '-'
ELSE r.[MOD]
END AS [MOD],
r.[FULLY IMPLEMENTED FACILITY PE RVU],
r.[FULLY IMPLEMENTED NON-FAC PE RVU],
r.[MP RVU],
c.Fac,
c.NonFac,
CASE WHEN c.mod IS NULL OR c.mod = '' THEN '1' WHEN c.mod = '26' then '2' ELSE '3' END AS row
FROM dbmasterdata.dbo.tblPROFEE AS c
LEFT JOIN dbmasterdata.dbo.tblPROFEE_RVU AS r ON c.HCPCS = r.HCPCS
AND ISNULL(r.MOD, N'') = ISNULL(c.MOD, N'')
WHERE c.mod <> '53'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.