logo
down
shadow

SQLServer - Multiple PIVOT on same columns


SQLServer - Multiple PIVOT on same columns

By : AJK
Date : November 21 2020, 01:01 AM
help you fix your problem To pivot multiple column you need to useMuliple Pivot's not multiple Aggregates. Try this.
code :
SELECT ID,
       NAME,
       Max([Raised to Supplier(PLANED)])[Raised to Supplier(PLANED)],
       Max([Base Test Date(PLANED)])[Base Test Date(PLANED)],
       Max([Washing Approval(PLANED)])[Washing Approval(PLANED)],
       Max([Raised to Supplier(ACTUAL)])[Raised to Supplier(ACTUAL)],
       Max([Base Test Date(ACTUAL)])[Base Test Date(ACTUAL)],
       Max([Washing Approval(ACTUAL)])[Washing Approval(ACTUAL)]
FROM   (SELECT 1 ID,'45rpm' NAME,'Raised to Supplier' + '(PLANED)' MSNAME_pl,'Raised to Supplier' + '(ACTUAL)' MSNAME_ac,'2014-12-17' PLANED,'2015-12-17' ACTUAL
        UNION ALL
        SELECT 1,'45rpm','Base Test Date' + '(PLANED)','Base Test Date' + '(ACTUAL)','2014-12-18','2015-12-18'
        UNION ALL
        SELECT 1,'45rpm','Washing Approval' + '(PLANED)','Washing Approval' + '(ACTUAL)','2014-12-19','2015-12-19') a
       PIVOT ( Max(PLANED)
             FOR MSNAME_pl IN ([Raised to Supplier(PLANED)],
                               [Base Test Date(PLANED)],
                               [Washing Approval(PLANED)]) ) AS p1 

        PIVOT ( MAX(ACTUAL) 
              FOR MSNAME_ac IN ([Raised to Supplier(ACTUAL)],
                                [Base Test Date(ACTUAL)],
                                [Washing Approval(ACTUAL)])) p2
GROUP BY ID, NAME


Share : facebook icon twitter icon
How to convert rows into columns using PIVOT in SqlServer

How to convert rows into columns using PIVOT in SqlServer


By : user3092051
Date : March 29 2020, 07:55 AM
Does that help I have this table: , If you know what the dimensions are, you can use cross join:
code :
select b.meaning as browser, m.meaning as module, o.meaning as os
from (select distinct meaning from thistable where dimension = 'browser'
     ) b cross join
     (select distinct meaning from thistable where dimension = 'module'
     ) m cross join
     (select distinct meaning from thistable where dimension = 'os'
     ) o;
Sqlserver PIVOT to turn a "reconstruct" a flat table into columns - why does this not work?

Sqlserver PIVOT to turn a "reconstruct" a flat table into columns - why does this not work?


By : Randal Mckissack
Date : March 29 2020, 07:55 AM
wish of those help The problem in your query is the pivot source query has GUID column which makes the pivot operator to consider GUID column.
To get the expected output you need to remove GUID column from the pivot source query.
code :
select * from
(
SELECT OrderGUID,UserDataCode,Value
    FROM
        tst) A
    PIVOT(Max(Value) 
          FOR UserDataCode IN ([OrderName],[OrderDesc],
                               [CerealYN],[ToastYN],
                              [ToastDesc],[PaperYN],
                              [PaperDesc])) AS PVTTable
pivot with dynamic date columns in SqlServer 2012 & MySQL

pivot with dynamic date columns in SqlServer 2012 & MySQL


By : user3937942
Date : March 29 2020, 07:55 AM
around this issue I have below data.
code :
Declare @Date1 date = '2017-05-01'
Declare @Date2 date = '2017-05-02'  -- Notice only Two Days


Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName([Date]) From #temp Where [Date] between @Date1 and @Date2  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select *
From (
        select [Date]
              ,B.*
         From #temp
         Cross Apply (values (''Orders'',orders)
                            ,(''Registrations'',registrations)
                     ) B(Item,Value)
         Where [Date] between '''+convert(varchar(10),@Date1,120)+''' and '''++convert(varchar(10),@Date2,120)+'''
     ) A
 Pivot (sum(Value) For [Date] in (' + @SQL + ') ) p'
Exec(@SQL);
Item            2017-05-01  2017-05-02
Orders          40          30
Registrations   30          60
How to pivot a set of multiple columns into a set of flagged values through DataFrame while not wanting to pivot all col

How to pivot a set of multiple columns into a set of flagged values through DataFrame while not wanting to pivot all col


By : agunga3d
Date : March 29 2020, 07:55 AM
With these it helps You can add column duration to id_vars, then to set_index and last groupby by both levels in Multiindex as follows:
code :
a = pd.melt(dfa, id_vars=['gameId', 'duration']) \
    .set_index(['gameId', 'duration'])['value'] \
    .str.get_dummies()
    .sum(level=[0,1])
print (a)
                 Annie  Bard  Lux  Olaf  Twitch
gameId duration                                
g1     15            1     1    0     0       0
g2     18            1     0    1     0       0
g3     15            0     0    0     1       1
a = pd.melt(dfa, id_vars=['gameId', 'duration']) \
    .set_index(['gameId', 'duration'])['value'] \
    .str.get_dummies() \
    .groupby(level=[0,1]) \
    .sum()
print (a)
                 Annie  Bard  Lux  Olaf  Twitch
gameId duration                                
g1     15            1     1    0     0       0
g2     18            1     0    1     0       0
g3     15            0     0    0     1       1
SQLServer 2008 PIVOT on multiple fields issue

SQLServer 2008 PIVOT on multiple fields issue


By : keemngi
Date : March 29 2020, 07:55 AM
this will help There are several ways to get the desired result. The easiest way might be to use an aggregate function with a CASE expression but you can also use the PIVOT function.
Aggregate with CASE:
Related Posts Related Posts :
  • Select records based on two tables where one column in one table starts with the records from another tables column usin
  • How to write select using two ids combination in Oracle 11g
  • Frequently use of 'UPDATE STATISTICS WITH FULLSCAN' is normal?
  • need query for this simple pulling data sql server
  • Sql query by using decode to show grades of employee
  • Not a single function error in Oracle
  • Oracle SQL optimizer's behavior when dealing with ORs and row-independent predicates (such as functions returning same v
  • Combining output of two or more select statement
  • Must Declare Scalar Variable Exception
  • Postgres Calculate Difference Using Window Functions
  • SQL Server : unsure how to retrieve selected records
  • SQL unique field: concurrency bugs?
  • Query to get the next identity?
  • SQL Server Adding summing values based on the month
  • Join on month if available else join on latest month
  • How to solve error: ORA-01406 fetched column value was truncated?
  • Selecting Field Multiple Times using OR
  • Which SQL datatype can be used to store mobile numbers in numeric forms, without including characters like brackets and
  • Oracle SQL - "Not a group by expression" after adding CASE statements
  • Runnning total of unique string values in column over days
  • VB.Net to AccessDB by SQL - INSERT INTO with parameters error
  • SQL/Excel/VBA - UPDATE query: 'Syntax Error (missing operator) in query expression'
  • Code to combine two sql queries
  • Setting SQL Date field value based off Week Number and Day Number Field
  • SQL query to find matching records but with differences in case sensitivity?
  • How to sort record inside views in SQL Server 2008
  • How to link the output of teradata sql query to an excel sheet?
  • Matching sets in SQL when cross-referencing
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org