I have been working on creating a PIVOT query in SQL Server. I have succeeded in creating the PIVOT query detailed below:

SELECT * FROM
(
SELECT ITM_VAL_NUM, YR, PRVDR_NUM
FROM     dbo.newfile
WHERE  (PRVDR_NUM = '410007') AND (WKSHT_CD = 'B000001') AND (LINE_NUM BETWEEN '00100' AND '02399') AND (CLMN_NUM = '00000') 
) t
PIVOT(
         SUM(ITM_VAL_NUM) 
         FOR YR IN (
         [2020],
         [2019],
         [2018],
         [2017])
) AS pivot_table;

I would like to expand the above PIVOT query to sum several rows of data based on row numbers. For example, the result above is for LINE_NUM 00100-02399. I would like to add another series of LINE_NUM from 03000-04699. I have tried (1) a joined table (2) using AND or OR without any luck.

SELECT * FROM
(
SELECT ITM_VAL_NUM, YR, PRVDR_NUM
FROM     dbo.newfile
WHERE  (PRVDR_NUM = '410007') AND (WKSHT_CD = 'B000001') AND (LINE_NUM BETWEEN '00100' AND '02399') AND (CLMN_NUM = '00000') 
OR (PRVDR_NUM = '410007') AND (WKSHT_CD = 'B000001') AND (LINE_NUM BETWEEN '03000 AND '04699) AND (CLMN_NUM = '00000')
) t
PIVOT(
         SUM(ITM_VAL_NUM) 
         FOR YR IN (
         [2020],
         [2019],
         [2018],
         [2017],
         [2016],
         [2015])
) AS pivot_table;

The desired outcome is as follows:

Thank you for taking the time to and answer my question.

CodePudding user response：

Not sure this will compile, because you did not post your data, however, you can see if it will create a result that you are looking for.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE newfile (PRVDR_NUM NVARCHAR(20),LINE_NUM NVARCHAR(20), ITM_VAL_NUM INT, YR INT)
INSERT newfile VALUES
('410007','00101',10,2020),
('410007','02000',10,2020),
('410007','00101',10,2019),
('410007','02100',10,2019),
('410007','00201',10,2018),
('410007','02700',10,2018),
('410007','00111',10,2017),
('410007','02400',10,2017),

('410007','03101',10,2020),
('410007','03300',10,2020),
('410007','04001',10,2019),
('410007','03100',10,2019),
('410007','03201',10,2018),
('410007','03700',10,2018),
('410007','03111',10,2017),
('410007','03400',10,2017)

Query 1:

CREATE TABLE #LINE_NUM_RANGE (LINE_NUM_START NVARCHAR(20),LINE_NUM_END NVARCHAR(20))
INSERT #LINE_NUM_RANGE VALUES
('00100','02399'),
('03000','04699')

Results:

Query 2:

SELECT * FROM
(
SELECT ITM_VAL_NUM, YR, PRVDR_NUM,LINE_NUM = R.LINE_NUM_START '-' R.LINE_NUM_END
FROM     newfile
INNER JOIN #LINE_NUM_RANGE R ON LINE_NUM  BETWEEN R.LINE_NUM_START AND LINE_NUM_END
WHERE  (PRVDR_NUM = '410007') --AND (WKSHT_CD = 'B000001') AND (CLMN_NUM = '00000') OR (PRVDR_NUM = '410007') AND (WKSHT_CD = 'B000001') 

) t
PIVOT(
         SUM(ITM_VAL_NUM) 
         FOR YR IN (
         [2020],
         [2019],
         [2018],
         [2017],
         [2016],
         [2015])
) AS pivot_table

Results:

| PRVDR_NUM |    LINE_NUM | 2020 | 2019 | 2018 | 2017 |   2016 |   2015 |
|-----------|-------------|------|------|------|------|--------|--------|
|    410007 | 00100-02399 |   20 |   20 |   10 |   10 | (null) | (null) |
|    410007 | 03000-04699 |   20 |   20 |   20 |   20 | (null) | (null) |