在SQL查询中的数据透视表列中的透视

I am new to SQL and learning writing Quires. Help me. thanks in Advance.

我是SQL的新手并且正在学习编写Quires。帮我。提前致谢。

Actually I am geeting the following Resultant table by using this query

实际上我使用此查询来调整以下的Resultant表

select * from 
(select case Temp 
           when '0' then 'Temperature' 
           when '1' then 'PULSE RATE/MIN'
           when '2' then 'RESPIRATION/MIN' 
           when '3' then 'BLOOD PRESSURE' 
           when '4' then 'URINE' 
        end as Temp,
        Value, 
        convert(nvarchar(15),Date,103) Date,
       concat(case Time 
                 when '0' then '2' 
                 when '1' then '6' 
                 when '2' then '10' 
              end,' ',
              case AMPM 
                 when '0' then 'AM' 
                 when '1' then 'PM' 
              end)[Tim]
from HMS_Chart_Clinical
where status = '0' and IPNO='21460') as s
pivot(max(Value) for [date] in ([07/11/2016],[08/11/2016])) as datapivot
pivot(max(Tim) for [Tim] in ([2 AM],[6 AM],[10 AM],[2 PM],[6 PM],[10 PM])) as datapivot

But I need the bellow Resultant table.

但我需要波纹管结果表。

2 个解决方案

#1

You cant have that table. The best you can do is create this columns instead and add the aditional header in the UI.

你不能拥有那张桌子。您可以做的最好是创建此列,并在UI中添加aditional标头。

 07/11/2016 02:00 am
 07/11/2016 06:00 am
 07/11/2016 10:00 am
 07/11/2016 02:00 pm
 07/11/2016 06:00 pm
 07/11/2016 10:00 pm
 08/11/2016 02:00 am
 08/11/2016 06:00 am
 08/11/2016 10:00 am
 08/11/2016 02:00 pm
 08/11/2016 06:00 pm
 08/11/2016 10:00 pm

So change your query to

所以将您的查询更改为

concat(convert(nvarchar(15),Date,103) Date,
       case Time 
             when '0' then '2' 
             when '1' then '6' 
             when '2' then '10' 
       end,' ',
       case AMPM 
             when '0' then 'AM' 
             when '1' then 'PM' 
       end) as [Tim]

and use a single pivot

并使用单个枢轴

pivot(max(Tim) for [Tim] in ([07/11/2016 02 AM],
                             [07/11/2016 06 AM],
                             [07/11/2016 10 AM],
                             [07/11/2016 02 PM],
                             [07/11/2016 06 PM],
                             [07/11/2016 10 PM]) .....) as datapivot

#2

I have issues with this that are unrelated to the question. So without affecting your original way of thinking too much:

我有与此问题无关的问题。所以不要过多地影响你原来的思维方式:

DECLARE @HMS_Chart_Clinical TABLE (
 [ID]     INT PRIMARY KEY NOT NULL IDENTITY (1,1),
 [Temp]   INT, --0,1,2,3,4
 [Value]  INT,
 [Date]   DATE,
 [Time]   INT,   -- 0,1,2
 [AMPM]   INT,   -- 0,1
 [status] BIT,
 [IPNO]   INT
)

INSERT @HMS_Chart_Clinical ([Temp], [Value], [Date], [Time], [AMPM], [status], [IPNO]) VALUES (3, 125, '20161107', 2, 0, 0, 21460)
INSERT @HMS_Chart_Clinical ([Temp], [Value], [Date], [Time], [AMPM], [status], [IPNO]) VALUES (1, 26, '20161108', 2, 1, 0, 21460)
INSERT @HMS_Chart_Clinical ([Temp], [Value], [Date], [Time], [AMPM], [status], [IPNO]) VALUES (1, 99, '20161107', 1, 1, 0, 21460)
INSERT @HMS_Chart_Clinical ([Temp], [Value], [Date], [Time], [AMPM], [status], [IPNO]) VALUES (0, 106, '20161107', 0, 0, 0, 21460)

IF OBJECT_ID('tempdb..#PivotSource') IS NOT NULL
    DROP TABLE #PivotSource

  CREATE TABLE #PivotSource
  (
   Temp       VARCHAR(MAX),
   Value      INT,
   [DateTime] VARCHAR(MAX)
  )
  INSERT INTO #PivotSource
  SELECT CASE Temp 
           WHEN '0' THEN 'Temperature' 
           WHEN '1' THEN 'PULSE RATE/MIN'
           WHEN '2' THEN 'RESPIRATION/MIN' 
           WHEN '3' THEN 'BLOOD PRESSURE' 
           WHEN '4' THEN 'URINE' 
         END AS Temp,
         Value, 
         CONVERT(NVARCHAR(15),[Date],103) + ' ' + 
         CASE [Time] 
           WHEN '0' THEN '2' 
           WHEN '1' THEN '6' 
           WHEN '2' THEN '10' 
         END + ' ' + 
         CASE AMPM 
            WHEN '0' THEN 'AM' 
            WHEN '1' THEN 'PM' 
         END AS [DateTime]
    FROM @HMS_Chart_Clinical
   WHERE [status] = '0' 
     AND IPNO='21460' 

DECLARE @PivotColumns NVARCHAR(MAX)

;WITH AllDatesAndTimes AS (
    SELECT DISTINCT [Date], 
           TimeString.[Time],
           TimeString.[SortOrder]
      FROM @HMS_Chart_Clinical
CROSS JOIN (SELECT '2 AM'  AS [Time], 0 AS SortOrder
  UNION ALL SELECT '6 AM'  AS [Time], 1 AS SortOrder
  UNION ALL SELECT '10 AM' AS [Time], 2 AS SortOrder
  UNION ALL SELECT '2 PM'  AS [Time], 3 AS SortOrder
  UNION ALL SELECT '6 PM'  AS [Time], 4 AS SortOrder
  UNION ALL SELECT '10 PM' AS [Time], 5 AS SortOrder) TimeString
)  
SELECT @PivotColumns = COALESCE(@PivotColumns + '], [', '') + CONVERT(NVARCHAR(15),[Date],103) + ' ' + [Time]
FROM AllDatesAndTimes
ORDER BY [Date],[SortOrder]

DECLARE @PivotQuery NVARCHAR(MAX) =  'SELECT * FROM #PivotSource  PIVOT(MAX(Value) FOR [DateTime] IN ([' + @PivotColumns + '])) AS DATAPIVOT'
EXEC(@PivotQuery)