logo
down
shadow

Dynamic Pivot - SQL Server


Dynamic Pivot - SQL Server

By : user2949953
Date : November 17 2020, 04:28 AM
To fix the issue you can do I have a test SQL database the following query: , Use Dynamic Query.
code :
DECLARE @col_list VARCHAR(max)='',
        @sel_list VARCHAR(max)='',
        @sql      NVARCHAR(max)

SELECT DISTINCT @col_list += '[' + Isnull(MonthID, '') + '],'
FROM   Test1
ORDER  BY MonthID

SELECT @col_list = LEFT(@col_list, Len(@col_list) - 1)

SELECT DISTINCT @sel_list += 'Isnull([' + Isnull(MonthID, '') + '],0) ' + '['+ MonthID + '],'
FROM   Test1
ORDER  BY MonthID

SELECT @sel_list = LEFT(@sel_list, Len(@sel_list) - 1)

SET @sql ='select Data.AccountID,Data.AccountName,'+ @sel_list+ ' from (
                Select
                Data.AccountID,
                Data.MonthID,
                Data.Sales,
                Data.AccountName
                From Test1 as Data ) A
                pivot (sum(Sales) for monthid in('+ @col_list + ')) piv'

--PRINT @sql
EXEC Sp_executesql @sql 


Share : facebook icon twitter icon
SQL Server Dynamic Pivot

SQL Server Dynamic Pivot


By : Alexander Novichenk
Date : March 29 2020, 07:55 AM
To fix the issue you can do Since you are concatenating empty strings in your dynamic SQL you need to add a few more single quotes:
code :
AND (isnull(cc.CLNT,'''') + isnull(cc1.CLNT,''''))<> '''') X
DECLARE @cols as NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT  DISTINCT ',' + QUOTENAME(CONVERT(VARCHAR(10),EVENTDTM,101)) as DATE 
                        FROM TIMESHEETITEM  WHERE DATEDIFF(d,EVENTDTM,GetDate()) <7 ORDER BY DATE DESC
                        FOR XML PATH(''), TYPE
                        ).value('.','NVARCHAR(MAX)')
                        ,1,1,'')

Set @query='SELECT EEID, ' + @cols + 'FROM (
SELECT p.PERSONnum as EEID,p.FULLNM as FULLNM, 
convert(varchar(10),ti.EVENTDTM,101) as PunchDate,
DATEDIFF(MINUTE,ti.enddtm,ti.startdtm)/60*-1 AS SPAN
FROM  (((((  
    TIMESHEETITEM ti  With (NoLock) LEFT JOIN  
        PUNCHEVENT pe With (NoLock) ON ti.STARTPUNCHEVENTID = pe.PUNCHEVENTID) LEFT JOIN 
            DATASOURCE ds ON pe.DATASOURCEID = ds.DATASOURCEID) LEFT JOIN 
                CLIENTCONTEXT cc ON ds.CLIENTCONTEXTID = cc.CLIENTCONTEXTID) 
    LEFT JOIN       PUNCHEVENT AS pe1  With (NoLock) ON ti.ENDPUNCHEVENTID = pe1.PUNCHEVENTID) LEFT JOIN 
            DATASOURCE AS ds1 ON pe1.DATASOURCEID = ds1.DATASOURCEID) LEFT JOIN 
                CLIENTCONTEXT AS cc1 ON ds1.CLIENTCONTEXTID = cc1.CLIENTCONTEXTID
    INNER JOIN PERSON p ON ti.EMPLOYEEID = p.PERSONID
    INNER JOIN LABORACCT la1 ON la1.laboracctid = ti.laboracctid
WHERE DATEDIFF(d,ti.EVENTDTM,GetDate()) <7
AND ti.TmShtItemTypeID = 40 
AND (isnull(cc.CLNT,'''') + isnull(cc1.CLNT,''''))<> '''') X
PIVOT
(
    SUM(SPAN) 
    FOR PunchDate in (' + @cols + ')
    ) p'

execute @query
SQL Server - Dynamic Pivot

SQL Server - Dynamic Pivot


By : Alex Neiman
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , Your current query is close but since you want to count the number of names you can just use that as the aggregate function, instead of using the CASE statement in your subquery and then averaging.
My suggestion would always be to write the query as a static version first before trying to write a dynamic SQL server, mainly to get the syntax correct:
code :
select place, Bad, New, OK
from
(
  select place, name
  from info_table
) d
pivot
(
  count(name)
  for name in (Bad, New, OK)
) p;
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT distinct 
                        ',' +
                        QUOTENAME(NAME)
                 FROM INFO_TABLE with (nolock)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @query = ' SELECT PLACE ,  ' + @cols + ' 

FROM 
(
   SELECT NAME, PLACE
   FROM INFO_TABLE with (nolock)
) t
PIVOT
( 
  count(name)
  FOR NAME IN (' + @cols + ' )
) 
p ' ;

Execute(@query);
Sql server dynamic pivot with dynamic fields type

Sql server dynamic pivot with dynamic fields type


By : wjb0016
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I found this working solution.
It is not complete (missing size of varchar and precision of numeric types) but it is enough to get the idea. The casting cannot be done on the pivot section and of course cannot work when querying the source table because types are mixed. The solution I found is to add casting to the dynamic column list, creating a simpler list (field names only) for the pivot and a longer (including cast) for the main select.
code :
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX), @ColumnsShort nVarChar(MAX);
SET @columnsShort = '';
SELECT @columnsShort  = @columnsShort + N', p.' + QUOTENAME(AttributeName)
  FROM (SELECT distinct p.AttributeName FROM MyData AS p
  INNER JOIN MyAttributes AS o
ON rtrim(p.AttributeName) = rtrim(o.AttributeName)
  ) AS x;

SET @columns = '';
SELECT @columns  = @columns + N', ' + attr
  FROM (SELECT distinct 'CAST(' + QUOTENAME(p.AttributeName) + ' AS ' + o.CastTo + ') AS ' + QUOTENAME(p.attributename) as attr FROM MyData AS p
  INNER JOIN MyAttributes AS o
ON rtrim(p.AttributeName) = rtrim(o.AttributeName)
  ) AS x;

SET @sql = N'
SELECT  [Key], ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT [key], p.AttributeName, attributevalue
   FROM dbo.MyData AS p
   INNER JOIN dbo.MyAttributes AS o
   ON rtrim(p.AttributeName) = rtrim(o.AttributeName)
) AS j
PIVOT
(
 max(AttributeValue) FOR AttributeName IN ('
  + STUFF(REPLACE(@columnsshort, ', p.[', ',['), 1, 1, '')
  + ') 
) AS p;';
PRINT @sql;

execute sp_executesql @sql
Dynamic Pivot Sql Server

Dynamic Pivot Sql Server


By : cruberg4
Date : March 29 2020, 07:55 AM
this one helps. First of all, try to use parameterized queries as much as possible and use string concatenation only when it's absolutely neccessery.
So by fixing that, your query should look somehow like that:
code :
DECLARE @ColumnName NVARCHAR(MAX) = '';
DECLARE @Query NVARCHAR(MAX) = '';
DECLARE @Site NVARCHAR(MAX) = '';
DECLARE @Date NVARCHAR(MAX) = '';
DECLARE @MOnth NVARCHAR(MAX) = '';

SELECT @ColumnName += ',' + QUOTENAME([Product Category])
FROM (
    SELECT DISTINCT [Product Category]
    FROM vw_TTMTALK_BREAKDOWN_DETAIL_LINE
    WHERE [Customer No] = 'SLPIP'
        AND DATEPART(yyyy, [Posting Date]) = '2016'
        AND CONVERT(CHAR(3), [Posting Date], 0) = 'Jan'
    GROUP BY [Customer No], [Product Category]
    ) AS T1;

SET @Site = 'SLPIP';
SET @Date = '2016';
SET @Month = 'Jan';

SET @Query = '
    SELECT *
    FROM (
        SELECT [Customer No], [Product Category]
        FROM vw_TTMTALK_BREAKDOWN_DETAIL_LINE 
        WHERE [Customer No] = @Site
            AND DATEPART(yyyy, [Posting Date]) = @Date
            AND CONVERT(CHAR(3), [Posting Date], 0) = @Month
        ) T2
    PIVOT (
        COUNT([Customer No])
        FOR [Product Category] IN (' + STUFF(@ColumnName, 1, 1, '') + ')
        ) T3';

EXEC sp_executesql @Query
    , N'@Site NVARCHAR(MAX), @Date NVARCHAR(MAX), @Month NVARCHAR(MAX)'
    , @Site
    , @Date
    , @Month;
AND DATEPART(yyyy, [Posting Date]) = '2016'
AND CONVERT(CHAR(3), [Posting Date], 0) = 'Jan'
AND [Posting Date] >= '2016-01-01'
AND [Posting Date] <  '2016-02-01';
Dynamic Pivot Queries with dynamic dates as column header in SQL Server

Dynamic Pivot Queries with dynamic dates as column header in SQL Server


By : user6381587
Date : March 29 2020, 07:55 AM
With these it helps This is the actual result set from SQL Server , Try this query..it will help you
code :
select * into #tempp from(

select '2016-01-17' as DATES,100 ORDERS,50 CANCELED_ORDERS
UNION ALL
SELECT '2016-01-18',120,20
UNION ALL
SELECT '2016-01-20',150,30
)AS A

--SELECT * FROM #tempp

declare @pivotcols nvarchar(max),@unpivotcols nvarchar(max),@SQLQUERY NVARCHAR(MAX)
select @pivotcols=stuff((select ','+quotename(dates) from #tempp for xml path('')),1,1,'')
--select @pivotcols

select @unpivotcols=stuff((select ','+name from tempdb.sys.columns where object_id =
object_id('tempdb..#tempp') and name<>'DATES' for xml path('')),1,1,'')
--select @unpivotcols

SET @SQLQUERY=N'select * from (
SELECT * FROM #tempp
)as a
unpivot (AMOUNTS FOR Dates  in ('+@unpivotcols+N') 
) AS UNPI
PIVOT (MAX(AMOUNTS) FOR DATES IN ('+@pivotcols+N')
)AS A'

PRINT @SQLQUERY

EXEC SP_EXECUTESQL @SQLQUERY
    +-----------------+------------+------------+------------+
    |      Dates       | 2016-01-17 | 2016-01-18 | 2016-01-20|
    +-----------------+------------+------------+------------+
    | CANCELED_ORDERS |         50 |         20 |         30 |
    | ORDERS          |        100 |        120 |        150 |
    +-----------------+------------+------------+------------+
Related Posts Related Posts :
  • How to create a database snapshot in SQL Server 2008 R2
  • Query speed and expressions with constant value
  • How to do a complex calculation as this sample
  • Passing a ResultSet into a Postgresql Function
  • Netsuite custom formula field using a CASE statement with multiple WHEN conditions returning "invalid expression&qu
  • Execute query command (dynamic sql) runs faster than ordinary query
  • How to extract only numbered rows from a column
  • INSTEAD OF DELETE trigger (Postgresql)
  • Oracle Apex, when a report with sql is created, how do i get it return after entering in to a text box?
  • 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
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org