데이터베이스 서버 유형 = SQL Server 2012 SP2
다음은 소스 테이블입니다.
CREATE TABLE a
(
e_id INT,
r_id INT,
d_id INT,
s_id INT
);
CREATE TABLE r
(
id INT,
NAME VARCHAR(50)
);
CREATE TABLE d
(
id INT,
calldate DATE
);
CREATE TABLE e
(
id INT,
NAME VARCHAR(50),
initial VARCHAR(5)
);
CREATE TABLE s
(
id INT,
specialty VARCHAR(50)
);
INSERT INTO a
VALUES (8,
13,
9,
13),
(8,
13,
10,
13),
(8,
13,
11,
13),
(8,
13,
12,
13),
(8,
13,
13,
13),
(8,
13,
14,
13),
(10,
13,
15,
13),
(10,
13,
16,
13),
(10,
13,
17,
13),
(16,
13,
18,
13),
(16,
13,
19,
13),
(16,
13,
20,
13),
(9,
13,
21,
13),
(9,
13,
22,
13),
(9,
13,
23,
13),
(9,
13,
24,
13),
(16,
13,
25,
13),
(16,
13,
26,
13);
INSERT INTO a
VALUES
(10,22,9,13),
(10,22,10,13),
(10,22,11,13),
(10,22,12,13),
(10,22,13,13),
(16,22,14,13),
(16,22,15,13),
(16,22,16,13),
(16,22,17,13),
(9,22,18,13),
(9,22,19,13),
(9,22,20,13),
(18,22,21,13),
(18,22,22,13),
(10,22,23,13),
(10,22,24,13),
(9,22,25,13),
(9,22,26,13);
INSERT INTO r
VALUES (13,
'Primary'),
(22,
'Secondary'),
(33,
'Prim Trd'),
(44,
'Sec Td');
INSERT INTO e
VALUES (8,
'Jack',
'JM'),
(9,
'Ace',
'AQ'),
(10,
'Mike',
'MM'),
(16,
'George',
'GH'),
(18 ,'Bill', 'BK' );
INSERT INTO s
VALUES (13,
'Pediatric'),
(28,
'EMT'),
(55,
'ER');
INSERT INTO d
VALUES (9 , '2015-01-09'),
(10 , '2015-01-10'),
(11 , '2015-01-11'),
(12 , '2015-01-12'),
(13 , '2015-01-13'),
(14 , '2015-01-14'),
(15 , '2015-01-15'),
(16 , '2015-01-16'),
(17 , '2015-01-17'),
(18 , '2015-01-18'),
(19 , '2015-01-19'),
(20 , '2015-01-20'),
(21 , '2015-01-21'),
(22 , '2015-01-22'),
(23 , '2015-01-23'),
(24 , '2015-01-24'),
(25 , '2015-01-25'),
(26 , '2015-01-26'),
(27 , '2015-01-27'),
(28 , '2015-01-28'),
(29 , '2015-01-29'),
(30 , '2015-01-30')
테이블 A는 테이블 D, E, R 및 S를 연결하는 연결 테이블입니다.
표 A
║ e_id │ r_id │ d_id │ s_id ║
╠══════╪══════╪══════╪══════╣
║ 8 │ 13 │ 9 │ 13 ║
║ 8 │ 13 │ 10 │ 13 ║
║ 8 │ 13 │ 11 │ 13 ║
║ 8 │ 13 │ 12 │ 13 ║
║ 8 │ 13 │ 13 │ 13 ║
║ 8 │ 13 │ 14 │ 13 ║
║ 10 │ 13 │ 15 │ 13 ║
║ 10 │ 13 │ 16 │ 13 ║
║ 10 │ 13 │ 17 │ 13 ║
║ 16 │ 13 │ 18 │ 13 ║
║ 16 │ 13 │ 19 │ 13 ║
║ 16 │ 13 │ 20 │ 13 ║
║ 9 │ 13 │ 21 │ 13 ║
║ 9 │ 13 │ 22 │ 13 ║
║ 9 │ 13 │ 23 │ 13 ║
║ 9 │ 13 │ 24 │ 13 ║
║ 16 │ 13 │ 25 │ 13 ║
║ 16 │ 13 │ 26 │ 13 ║
║ 10 │ 22 │ 9 │ 13 ║
║ 10 │ 22 │ 10 │ 13 ║
║ 10 │ 22 │ 11 │ 13 ║
║ 10 │ 22 │ 12 │ 13 ║
║ 10 │ 22 │ 13 │ 13 ║
║ 16 │ 22 │ 14 │ 13 ║
║ 16 │ 22 │ 15 │ 13 ║
║ 16 │ 22 │ 16 │ 13 ║
║ 16 │ 22 │ 17 │ 13 ║
║ 9 │ 22 │ 18 │ 13 ║
║ 9 │ 22 │ 19 │ 13 ║
║ 9 │ 22 │ 20 │ 13 ║
║ 18 │ 22 │ 21 │ 13 ║
║ 18 │ 22 │ 22 │ 13 ║
║ 10 │ 22 │ 23 │ 13 ║
║ 10 │ 22 │ 24 │ 13 ║
║ 9 │ 22 │ 25 │ 13 ║
║ 9 │ 22 │ 26 │ 13 ║
표 D
║ id │ calldate ║
╠════╪════════════╣
║ 9 │ 2015-01-09 ║
║ 10 │ 2015-01-10 ║
║ 11 │ 2015-01-11 ║
║ 12 │ 2015-01-12 ║
║ 13 │ 2015-01-13 ║
║ 14 │ 2015-01-14 ║
║ 15 │ 2015-01-15 ║
║ 16 │ 2015-01-16 ║
║ 17 │ 2015-01-17 ║
║ 18 │ 2015-01-18 ║
║ 19 │ 2015-01-19 ║
║ 20 │ 2015-01-20 ║
║ 21 │ 2015-01-21 ║
║ 22 │ 2015-01-22 ║
║ 23 │ 2015-01-23 ║
║ 24 │ 2015-01-24 ║
║ 25 │ 2015-01-25 ║
║ 26 │ 2015-01-26 ║
║ 27 │ 2015-01-27 ║
║ 28 │ 2015-01-28 ║
║ 29 │ 2015-01-29 ║
║ 30 │ 2015-01-30 ║
표 E
╔════╤════════╤═════════╗
║ id │ name │ initial ║
╠════╪════════╪═════════╣
║ 8 │ Jack │ JM ║
║ 9 │ Ace │ AQ ║
║ 10 │ Mike │ MM ║
║ 16 │ George │ GH ║
║ 18 │ Bill │ BK ║
╚════╧════════╧═════════╝
표 R
╔════╤═══════════╗
║ id │ name ║
╠════╪═══════════╣
║ 13 │ Primary ║
║ 22 │ Secondary ║
║ 33 │ Prim Trd ║
║ 44 │ Sec Td ║
╚════╧═══════════╝
표 S
|ID | Specialty |
|---+-----------|
|13 | Pediatric |
|28 | EMT |
|55 | ER |
다음은 작동한다고 생각했지만 오류가 발생하는 쿼리입니다.
SELECT d.calldate,
Max(CASE
WHEN s.id = 13
AND r.NAME = 'Primary' THEN ( e.NAME )
END) AS [Primary],
Max(CASE
WHEN s.id = 13
AND r.NAME = 'Secondary' THEN ( e.NAME )
END) AS [Secondary],
Max(CASE
WHEN s.id = 13
AND r.NAME = 'Primary'
AND ( ( e.id = (SELECT e1.id
FROM a a1
INNER JOIN e e1
ON e1.id = a1.e_id
INNER JOIN d d1
ON d1.id = a1.d_id
INNER JOIN s s1
ON s1.id = a1.s_id
INNER JOIN r r1
ON r1.id = a1.r_id
WHERE d1.calldate = CONVERT(DATE,
Dateadd(dd, -1,
d.calldate),
101)
AND r1.NAME = 'Primary'
AND s1.id = 13) )
AND ( e.id = (SELECT e1.id
FROM a a1
INNER JOIN e e1
ON e1.id = a1.e_id
INNER JOIN d d1
ON d1.id = a1.d_id
INNER JOIN s s1
ON s1.id = a1.s_id
INNER JOIN r r1
ON r1.id = a1.r_id
WHERE d1.calldate = CONVERT(DATE, Dateadd
(dd, 1,
d.calldate),
101
)
AND r1.NAME = 'Primary'
AND s1.id = 13) ) ) THEN ' '
ELSE e.initial
END) AS [PrimaryINIT],
Max(CASE
WHEN s.id = 13
AND r.NAME = 'Secondary'
AND ( e.id = (SELECT e1.id
FROM a a1
INNER JOIN e e1
ON e1.id = a1.e_id
INNER JOIN d d1
ON d1.id = a1.d_id
INNER JOIN s s1
ON s1.id = a1.s_id
INNER JOIN r r1
ON r1.id = a1.r_id
WHERE d1.calldate = CONVERT(DATE, Dateadd(dd, -1,
d.calldate),
101)
AND r1.NAME = 'Secondary'
AND s1.id = 13)
AND ( e.id = (SELECT e1.id
FROM a a1
INNER JOIN e e1
ON e1.id = a1.e_id
INNER JOIN d d1
ON d1.id = a1.d_id
INNER JOIN s s1
ON s1.id = a1.s_id
INNER JOIN r r1
ON r1.id = a1.r_id
WHERE d1.calldate = CONVERT(DATE, Dateadd(dd, 1,
d.calldate),
101
)
AND r1.NAME = 'Secondary'
AND s1.id = 13) ) ) THEN ' '
ELSE e.initial
END) AS [SecondaryINIT]
FROM a
INNER JOIN e
ON e.id = a.e_id
INNER JOIN d
ON d.id = a.d_id
INNER JOIN s
ON s.id = a.s_id
INNER JOIN r
ON r.id = a.r_id
WHERE Datepart(mm, d.calldate) = 1
AND Datepart(year, d.calldate) = 2015
GROUP BY d.calldate
다음은 쿼리에서 찾고있는 결과입니다.
(초기 열은 데이터 변경 전후에만 열 값을 반환합니다.)
쿼리에서 원하는 결과.
╔════════════╤═════════╤═══════════╤═════════╤═══════════╗
║ callDate │ Primary │ Secondary │ PrimINIT│ SeconINIT ║
╠════════════╪═════════╪═══════════╪═════════╪═══════════╣
║ 2015-01-09 │ Jack │ Mike │ JM │ MM ║
║ 2015-01-10 │ Jack │ Mike │ │ ║
║ 2015-01-11 │ Jack │ Mike │ │ ║
║ 2015-01-12 │ Jack │ Mike │ │ ║
║ 2015-01-13 │ Jack │ Mike │ │ MM ║
║ 2015-01-14 │ Jack │ George │ JM │ GH ║
║ 2015-01-15 │ Mike │ George │ MM │ ║
║ 2015-01-16 │ Mike │ George │ │ ║
║ 2015-01-17 │ Mike │ George │ MM │ GH ║
║ 2015-01-18 │ George │ Ace │ GH │ AQ ║
║ 2015-01-19 │ George │ Ace │ │ ║
║ 2015-01-20 │ George │ Ace │ GH │ AQ ║
║ 2015-01-21 │ Ace │ Bill │ AQ │ BK ║
║ 2015-01-22 │ Ace │ Bill │ │ BK ║
║ 2015-01-23 │ Ace │ Mike │ │ MM ║
║ 2015-01-24 │ Ace │ Mike │ AQ │ MM ║
║ 2015-01-25 │ George │ Ace │ GH │ AQ ║
║ 2015-01-26 │ George │ Ace │ GH │ AQ ║
╚════════════╧═════════╧═══════════╧═════════╧═══════════╝
다음과 같이 시도하십시오.
WITH Pivoted AS
(
SELECT d.calldate AS calldate
,MAX(CASE WHEN r.Name='Primary' THEN e.NAME END) AS [Primary]
,MAX(CASE WHEN r.Name='Secondary' THEN e.NAME END) AS [Secondary]
,MAX(CASE WHEN r.Name='Primary' THEN e.initial END) AS [PrimINIT]
,MAX(CASE WHEN r.Name='Secondary' THEN e.initial END) AS [SeconINIT]
FROM a
INNER JOIN e ON a.e_id=e.id
INNER JOIN r ON a.r_id=r.id
INNER JOIN d ON a.d_id=d.id
INNER JOIN s ON a.s_id=s.id
GROUP BY d.calldate
)
,WithNeighbours AS
(
SELECT *
,ISNULL(LAG([Primary]) OVER(ORDER BY calldate,[Primary]),'') AS PrevPrim
,ISNULL(LAG([Secondary]) OVER(ORDER BY calldate,[Secondary]),'') AS PrevSecon
,ISNULL(LEAD([Primary]) OVER(ORDER BY calldate,[Primary]),'') AS NxtPrim
,ISNULL(LEAD([Secondary]) OVER(ORDER BY calldate,[Secondary]),'') AS NxtSecon
FROM Pivoted
)
SELECT calldate
,[Primary]
,[Secondary]
,CASE WHEN [Primary]<>PrevPrim OR [Primary]<>NxtPrim THEN PrimINIT ELSE '' END AS PrimINIT
,CASE WHEN [Secondary]<>PrevSecon OR [Secondary]<>NxtSecon THEN SeconINIT ELSE '' END AS SeconINIT
FROM WithNeighbours
ORDER BY calldate
주어진 데이터의 결과
+------------+---------+-----------+----------+-----------+
| calldate | Primary | Secondary | PrimINIT | SeconINIT |
+------------+---------+-----------+----------+-----------+
| 2015-01-09 | Jack | Mike | JM | MM |
+------------+---------+-----------+----------+-----------+
| 2015-01-10 | Jack | Mike | | |
+------------+---------+-----------+----------+-----------+
| 2015-01-11 | Jack | Mike | | |
+------------+---------+-----------+----------+-----------+
| 2015-01-12 | Jack | Mike | | |
+------------+---------+-----------+----------+-----------+
| 2015-01-13 | Jack | Mike | | MM |
+------------+---------+-----------+----------+-----------+
| 2015-01-14 | Jack | George | JM | GH |
+------------+---------+-----------+----------+-----------+
| 2015-01-15 | Mike | George | MM | |
+------------+---------+-----------+----------+-----------+
| 2015-01-16 | Mike | George | | |
+------------+---------+-----------+----------+-----------+
| 2015-01-17 | Mike | George | MM | GH |
+------------+---------+-----------+----------+-----------+
| 2015-01-18 | George | Ace | GH | AQ |
+------------+---------+-----------+----------+-----------+
| 2015-01-19 | George | Ace | | |
+------------+---------+-----------+----------+-----------+
| 2015-01-20 | George | Ace | GH | AQ |
+------------+---------+-----------+----------+-----------+
| 2015-01-21 | Ace | Bill | AQ | BK |
+------------+---------+-----------+----------+-----------+
| 2015-01-22 | Ace | Bill | | BK |
+------------+---------+-----------+----------+-----------+
| 2015-01-23 | Ace | Mike | | MM |
+------------+---------+-----------+----------+-----------+
| 2015-01-24 | Ace | Mike | AQ | MM |
+------------+---------+-----------+----------+-----------+
| 2015-01-25 | George | Ace | GH | AQ |
+------------+---------+-----------+----------+-----------+
| 2015-01-26 | George | Ace | GH | AQ |
+------------+---------+-----------+----------+-----------+
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다