How to bring table values into temp table

Question

I have a sql query where SQL Server outputs data to looks like this

ScheduledAppts	KeptAppts	UnkeptAppts
30	20	10

And I want to alter this output into a sql server #temp table so it looks like this:

Category	Count
ScheduledAppts	30
KeptAppts	20
UnkeptAppts	10

Been trying to use Pivot but I think I'm doing it wrong.

Code:

SELECT

         COUNT(x.Scheduled) AS ScheduledAppts,

         COUNT(x.KeptEncounters) AS KeptAppts,

         COUNT(x.UnkeptEncounters) AS UnkeptAppts

   FROM (

         SELECT DISTINCT

                  COUNT(frz.TotalAppt) AS Scheduled,

                  CASE

                       WHEN frz.PDEncounters > 0 THEN

                       COUNT(frz.PDEncounters)

                  END AS KeptEncounters,

                  CASE

                       WHEN frz.PDEncounters = 0 THEN

                       COUNT(frz.PDEncounters)

                  END AS UnkeptEncounters,


           FROM [CDW].[dbo].[Fact_FREEZEPOLICE] frz

   ) x

On the specific point of temp tables.. Also take a look at CTE's. (Common Table Expressions) - using the `with` clause will often will scratch that itch! ;-) — JGFMK, May 19 '21 at 16:32

Amit11794 · Answer 1 · 2021-05-20T05:20:14.917

You actually want to UNPIVOT here -

DECLARE @T TABLE (ScheduledAppts INT, KeptAppts INT, UnkeptAppts INT)

INSERT INTO @T (ScheduledAppts, KeptAppts, UnkeptAppts)
SELECT 30, 20, 10

SELECT [Category]
    ,[Count]
FROM (
    SELECT ScheduledAppts
        ,KeptAppts
        ,UnkeptAppts
    FROM @T
    ) P
UNPIVOT([Count] FOR [Category] IN (
            ScheduledAppts
            ,KeptAppts
            ,UnkeptAppts
            )) AS UnPvt

Output:

Category	Count
ScheduledAppts	30
KeptAppts	20
UnkeptAppts	10

Reference: Converting Columns into rows with their respective data in sql server

score 0 · Answer 2 · answered May 19 '21 at 16:30

You can use cross apply:

select v.*
from output o cross apply
     (values ('ScheduledAppts', ScheduledAppts),
             ('KeptAppts', KeptAppts),
             ('UnkeptAppts', UnkeptAppts)
     ) v(category, count);

Note: You might find it simpler to change the original query, if the original "table" is really a query result.

How to bring table values into temp table

2 Answers2