logo
down
shadow

How to create a SQL Server Pivot Table


How to create a SQL Server Pivot Table

By : user2172987
Date : October 22 2020, 08:10 AM
To fix this issue https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=915122598b6f8f350127f79e21b3c12c
You need to change your dates into literal values to use in the pivot.
code :
SELECT
  *
FROM
(
  SELECT
    DESCRIPTION_,
    DISPLAYNAME,
    CAST(DATEDIFF(DAY, FINISHDATETIME, GETDATE()) AS VARCHAR(1))  AS dateNum,
    SUCCESFULRECORDCOUNT
  FROM
    test
  WHERE
    FINISHDATETIME >= CAST(DATEADD(DAY, -4, GETDATE()) AS DATE)
)
  data
PIVOT
(
  SUM(SUCCESFULRECORDCOUNT) FOR dateNum IN ([4], [3], [2], [1])
)
  pvt
;


Share : facebook icon twitter icon
Create table on SQL Server from dynamic pivot results

Create table on SQL Server from dynamic pivot results


By : วัชรี ณ. ลำพูน
Date : March 29 2020, 07:55 AM
I wish this help you Unless I am not following what you are trying to do you should be able to add the INTO mynewTable to your sql that you are going to execute and you should get the new table.
code :
declare @pivot varchar(max), @sql varchar(max)
create table pivot_columns (pivot_column varchar(100))

insert into pivot_columns
select distinct DateField from MyTable order by 1

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from pivot_columns

set @sql = 'SELECT * INTO mynewTable FROM (select DateField, RefCode, SumField from MyTable) p
PIVOT
(sum(SumField) FOR DateField IN  ( ' + @pivot + ') ) 
AS pvl'

drop table pivot_columns

exec (@sql)
create table t
( 
    [month] int, 
    [id] nvarchar(20), 
    [cnt] int 
)

insert t values (4,'TOTAL',214)
insert t values (5,'TOTAL',23)
insert t values (6,'TOTAL',23)
insert t values (4,'FUNC',47)
insert t values (5,'FUNC',5)
insert t values (6,'FUNC',5)
insert t values (4,'INDIL',167)
insert t values (5,'INDIL',18)
insert t values (6,'INDIL',18)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month) 
            FROM t 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *
            INTO tabletest from 
            (
                select month, id, cnt
                from t
           ) x
            pivot 
            (
                 sum(cnt)
                for month in (' + @cols + ')
            ) p '


execute(@query)

drop table t
How to create a dynamic pivot table SQL Server 2008

How to create a dynamic pivot table SQL Server 2008


By : Paweł Jóźwik
Date : March 29 2020, 07:55 AM
I hope this helps . That is what I have in my SQL Server 2008 database, I am trying pivot table but having hard time to get it to work , First, a way to do it:
code :
create table #tab (
            Username nvarchar(100),
            [Date] date,
            [hours] numeric(8,1)
        )

insert #tab values 
    ('John', '1995-02-01', 45),
    ('John', '1995-02-01', 16),
    ('Nancy', '1998-03-01', 25),
    ('John', '2001-05-01', 35.5),
    ('Peter', '1995-02-01', 46),
    ('Bill', '2001-05-01', 48),
    ('Bill', '1995-02-01', 56)


select  pvt.*
    from (
        select  Username, 
                datename(month, [Date]) + ' ' + convert(nvarchar(20), datepart(year, [Date])) [Date], 
                [hours] 
            from #tab
        ) t
    pivot (
        SUM([hours])
        for [Date] in ([February 1995], [March 1998], [May 2001])
        ) pvt

drop table #tab
create table #tab (
            Username nvarchar(100),
            [Date] date,
            [hours] numeric(8,1)
        )

insert #tab values 
    ('John', '1995-02-01', 45),
    ('John', '1995-02-01', 16),
    ('Nancy', '1998-03-01', 25),
    ('John', '2001-05-01', 35.5),
    ('Peter', '1995-02-01', 46),
    ('Bill', '2001-05-01', 48),
    ('Bill', '1995-02-01', 56)

declare @columns nvarchar(max) = ''
declare @sql nvarchar(max) = ''
declare @delim nvarchar(10) = ''

select  @columns = @columns + @delim + '[' + x.[Date] + ']',
        @delim = ', '
    from (  
        select  datename(month, t.[Date]) + ' ' + convert(nvarchar(20), datepart(year, t.[Date])) [Date],
                datepart(year, t.[Date]) [Year],
                datepart(month, t.[Date]) [Month]
            from #tab t
        ) x
    group by x.[Date]
    order by MAX(x.[Year]), MAX(x.[Month])


select @columns

set @sql = '
    select  pvt.Username,
            ' + @columns + '
        from (
            select  Username, 
                    datename(month, [Date]) + '' '' + convert(nvarchar(20), datepart(year, [Date])) [Date], 
                    [hours] 
                from #tab
            ) t
        pivot (
            SUM([hours])
            for [Date] in (' + @columns + ')
            ) pvt
    '

exec(@sql)

drop table #tab
Need to Create Pivot on the table in SQL Server

Need to Create Pivot on the table in SQL Server


By : mzetvek
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I have a table for Assessment with the following columns :- , You can use dynamic query to acheive this.
code :
Create TABLE #Table1 
    ([AssessmentID] nvarchar(50), [QuestionID] nvarchar(50), [Answer] nvarchar(50));


INSERT INTO #Table1
    ([AssessmentID], [QuestionID], [Answer])
VALUES
    ('1', '1.1', 'Test1'),
    ('1', '1.2', 'Test2'),
    ('1', '1.3', 'Test'),
    ('1', '1.4', 'Test1'),
    ('1', '2', 'Test2'),
    ('1', '2.3',null)
;

DECLARE
@cols AS NVARCHAR(MAX)='',
@sql  AS NVARCHAR(MAX)='';


SET @cols = STUFF(
(SELECT N',' + QUOTENAME([QuestionID]) AS [text()]
FROM #Table1 AS Y
FOR XML PATH('')),
1, 1, N'');
print @cols
SET @sql = N'SELECT *
FROM #Table1
PIVOT(MAX([Answer]) FOR QuestionID IN(' + @cols + N')) AS P;';


EXEC sp_executesql @sql;
print @sql;
GO

drop table #table1
Create Pivot query in SQL Server like Excel pivot table

Create Pivot query in SQL Server like Excel pivot table


By : Nefeli Hal
Date : March 29 2020, 07:55 AM
like below fixes the issue I am trying to convert an Excel pivot to a SQL query so instead of extracting the data first from my SQL Server database then create the pivot table manually in Excel, I want to create the pivot query directly in SQL. , You can try to use CASE WHEN and SUM function to make it.
code :
SELECT Brand,
       SUM(CASE WHEN Location = 'Austria' THEN qty END) 'Austria_qty',
       SUM(CASE WHEN Location = 'Austria' THEN Price  END) 'Austria_totle',
       SUM(CASE WHEN Location = 'France' THEN qty  END) 'France_qty',
       SUM(CASE WHEN Location = 'France' THEN Price END)'France_totle',
       SUM(CASE WHEN Location = 'Germany' THEN qty END) 'Germany_qty',
       SUM(CASE WHEN Location = 'Germany' THEN Price END)'Germany_totle',
       SUM(CASE WHEN Location = 'Italy' THEN qty END) 'Italy_qty',
       SUM(CASE WHEN Location = 'Italy' THEN Price END) 'Italy_totle'
FROM T
GROUP BY Brand
|   Brand | Austria_qty |      Austria_totle | France_qty | France_totle | Germany_qty | Germany_totle | Italy_qty | Italy_totle |
|---------|-------------|--------------------|------------|--------------|-------------|---------------|-----------|-------------|
|   Apple |           1 |            1351.16 |          1 |         9.96 |           2 |       1583.85 |         1 |     1053.83 |
|  Huawei |           1 |             744.67 |     (null) |       (null) |           2 |     207704.86 |    (null) |      (null) |
|  Lenovo |           2 |            1184.21 |          2 |      1420.43 |           2 |       3454.91 |    (null) |      (null) |
|   Nokia |      (null) |             (null) |          1 |       796.03 |      (null) |        (null) |         1 |      538.41 |
| Samsung |      (null) |             (null) |          1 |      3327.14 |      (null) |        (null) |         1 |        9.09 |
;WITH CTE(Brand,Austria_qty,Austria_totle,France_qty,France_totle,Germany_qty,Germany_totle,Italy_qty,Italy_totle) 
AS (
  SELECT Brand,
         SUM(CASE WHEN Location = 'Austria' THEN qty END) 'Austria_qty',
         SUM(CASE WHEN Location = 'Austria' THEN Price  END) 'Austria_totle',
         SUM(CASE WHEN Location = 'France' THEN qty  END) 'France_qty',
         SUM(CASE WHEN Location = 'France' THEN Price END)'France_totle',
         SUM(CASE WHEN Location = 'Germany' THEN qty END) 'Germany_qty',
         SUM(CASE WHEN Location = 'Germany' THEN Price END)'Germany_totle',
         SUM(CASE WHEN Location = 'Italy' THEN qty END) 'Italy_qty',
         SUM(CASE WHEN Location = 'Italy' THEN Price END) 'Italy_totle'
  FROM T
  GROUP BY Brand
)

SELECT Brand,
    Austria_qty,
    Austria_totle,
    France_qty,
    France_totle,
    Germany_qty,
    Germany_totle,
    Italy_qty,
    Italy_totle 
FROM CTE
UNION ALL 
SELECT 'Totle',
    SUM(Austria_qty),
    SUM(Austria_totle),
    SUM(France_qty),
    SUM(France_totle),
    SUM(Germany_qty),
    SUM(Germany_totle),
    SUM(Italy_qty),
    SUM(Italy_totle) 
FROM CTE
SELECT tt.brand,
       SUM(tt.austria_qty) 'austria_qty',
       SUM(tt.austria_totle) 'austria_totle',
       SUM(tt.france_qty) 'austria_qty',
       SUM(tt.france_totle) 'austria_totle',
       SUM(tt.germany_qty) 'austria_qty',
       SUM(tt.germany_totle) 'austria_totle',
       SUM(tt.italy_qty) 'austria_qty',
       SUM(tt.italy_totle) 'austria_totle'
FROM T CROSS APPLY (
  VALUES (Brand
          ,(CASE WHEN [Location] = 'Austria' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'Austria' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'France' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'France' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'Germany' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'Germany' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'Italy' THEN [qty] END)
          ,(CASE WHEN [Location] = 'Italy' THEN [Price] END) 
         ),
         (
         'Totle'
          ,(CASE WHEN [Location] = 'Austria' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'Austria' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'France' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'France' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'Germany' THEN [qty] END) 
          ,(CASE WHEN [Location] = 'Germany' THEN [Price] END) 
          ,(CASE WHEN [Location] = 'Italy' THEN [qty] END)
          ,(CASE WHEN [Location] = 'Italy' THEN [Price] END) 
         )
) tt(brand, 
     austria_qty, 
     austria_totle, 
     france_qty,  
     france_totle, 
     germany_qty, 
     germany_totle, 
     italy_qty, 
     italy_totle)
GROUP BY tt.brand
|   Brand | Austria_qty |      Austria_totle | France_qty |       France_totle | Germany_qty | Germany_totle | Italy_qty |        Italy_totle |
|---------|-------------|--------------------|------------|--------------------|-------------|---------------|-----------|--------------------|
|   Apple |           1 |            1351.16 |          1 |               9.96 |           2 |       1583.85 |         1 |            1053.83 |
|  Huawei |           1 |             744.67 |     (null) |             (null) |           2 |     207704.86 |    (null) |             (null) |
|  Lenovo |           2 | 1184.2099999999998 |          2 |            1420.43 |           2 |       3454.91 |    (null) |             (null) |
|   Nokia |      (null) |             (null) |          1 |             796.03 |      (null) |        (null) |         1 |             538.41 |
| Samsung |      (null) |             (null) |          1 |            3327.14 |      (null) |        (null) |         1 |               9.09 |
|   Totle |           4 |            3280.04 |          5 | 5553.5599999999995 |           6 |     212743.62 |         3 | 1601.3299999999997 |
Create Matrix table using Pivot in Sql server

Create Matrix table using Pivot in Sql server


By : user2090940
Date : March 29 2020, 07:55 AM
I hope this helps . Hi Just Made some correction to your existed sql script which will give exact result that you expected try it
code :
    DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(TabHeader)

                    from temptes
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT recordnum ,' + @cols + ' from 
             (
                select *
                from temptes
            ) x
            pivot 
            (
                max(tabvalue)
                for TabHeader in (' + @cols + ')
            ) p '
PRINT @query
execute(@query)
Recordnum    A        B       C
----------------------------------
1           data1   data2   data3
2           Data4   data5   data6
3           Data7   NULL    NULL
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org