logo
down
shadow

SQL Pivot Questions


SQL Pivot Questions

By : Jared Burch
Date : November 19 2020, 03:01 PM
this will help I have data like below: , This is unpivot, but I prefer using apply:
code :
select v.*
from t outer apply
     (values (t.year, 'January', t.January),
             (t.year, 'Feb', t.Feb),
             (t.year, 'March', t.March),
             (t.year, 'April', t.April)
     ) v(year, month, value);


Share : facebook icon twitter icon
Poor Man's SQL Pivot. List Questions as Columns and Answers per User in one row

Poor Man's SQL Pivot. List Questions as Columns and Answers per User in one row


By : arkoan
Date : March 29 2020, 07:55 AM
like below fixes the issue Current query: , This is the MSSQL Version
code :
select o.*, q1.[Type of Surgery:], q2.[Month of Surgery:], q3.[Year of surgery:]
    , q4.[Current Ostomy System Brand:]
    , q5.[Degree of Satisfaction with the fit and comfort of your Current Ostomy System:]
from (
    select distinct ordID, ordName + ' ' + ordLastName as [name] from dbo.Orders
) o
left join (
    select *, a.[Answer] as [Type of Surgery:] from cart_survey cs
    left join dbo.survey_answers a on cs.answer_id = a.id
    where cs.question_id = 1
) q1 on o.ordID = q1.[order_id]
left join (
    select *, a.[Answer] as [Month of Surgery:] from cart_survey cs
    left join dbo.survey_answers a on cs.answer_id = a.id
    where cs.question_id = 2
) q2 on o.ordID = q2.[order_id]
left join (
    select *, a.[Answer] as [Year of surgery:] from cart_survey cs
    left join dbo.survey_answers a on cs.answer_id = a.id
    where cs.question_id = 3
) q3 on o.ordID = q3.[order_id]
left join (
    select *, a.[Answer] as [Current Brand:] from cart_survey cs
    left join dbo.survey_answers a on cs.answer_id = a.id
    where cs.question_id = 4
) q4 on o.ordID = q4.[order_id]
left join (
    select *, a.[Answer] as [Degree of Satisfaction:] from cart_survey cs
    left join dbo.survey_answers a on cs.answer_id = a.id
    where cs.question_id = 5
) q5 on o.ordID = q5.[order_id]
SQL Server : 2 Pivot Questions

SQL Server : 2 Pivot Questions


By : Mustafa Shairani
Date : March 29 2020, 07:55 AM
should help you out Both queries are easy to do in a dynamic fashion, which is nice if you don't have a fixed number of rows and need the query to adapt to the number of rows.
The first query:
code :
DECLARE @players AS VARCHAR(MAX)
SELECT @players = STUFF((SELECT DISTINCT ',['+CAST(cid AS VARCHAR(10))+']'
FROM customers FOR XML PATH('')),1,1,'')
DECLARE @dynamic_pivot_query AS VARCHAR(MAX)

SET @dynamic_pivot_query = 'SELECT '+@players+'
  FROM (SELECT cid, name FROM customers) AS S
  PIVOT (MAX(name) FOR cid IN ('+@players+')
) AS P'

EXEC(@dynamic_pivot_query)
1       2       3       4       5       6       7           8
Messi   Ronaldo Weah    Nedved  Hagi    Puskas  Stoichkov   Di Baggio
DECLARE @cities AS VARCHAR(MAX)
SELECT @cities = STUFF((SELECT DISTINCT ',['+city+']'
FROM customers FOR XML PATH('')),1,1,'')

DECLARE @dynamic_pivot_query AS VARCHAR(MAX)
SET @dynamic_pivot_query = 'SELECT '+@cities+' 
   FROM (SELECT city FROM customers) as S
   PIVOT (COUNT(city) FOR city IN ('+@cities+')
) AS P'

EXEC(@dynamic_pivot_query)
Dubai       Graz        Istanbul    Wien
----------- ----------- ----------- -----------
2           2           1           3
Excel Power Query; How to merge values and pivot the questions

Excel Power Query; How to merge values and pivot the questions


By : Frank
Date : March 29 2020, 07:55 AM
wish helps you Assuming question text remains constant try the following and feedback on any problems. I have used the example from your screen shot link.
A couple of questions: 1. toggle pivot high and low function on or off. 2. change the label offset distance

A couple of questions: 1. toggle pivot high and low function on or off. 2. change the label offset distance


By : lama d'oct
Date : March 29 2020, 07:55 AM
I wish did fix the issue. It's simpler to keep the condition out of the function. Also, would be faster to just hide the indicator with the Show/Hide eye icon when you don't want it to show.
code :
//@version=4
study(title="testmajiggy", overlay=true)

pvhl = input(true, title="pivot hl on")
lenH = input(title="Length High", type=input.integer, defval=100, minval=1)
lenL = input(title="Length Low", type=input.integer, defval=100, minval=1)
atrM = input(0.5, "Price Offset (Multiple of ATR)", minval = 0.0, step = 0.1)
atrD = atr(5) * atrM

fun(src, len, isHigh, _style, _yloc, _color, _offset) =>
    p = nz(src[len])
    isFound = true
    for i = 0 to len - 1
        if isHigh and src[i] > p
            isFound := false

        if not isHigh and src[i] < p
            isFound := false

    for i = len + 1 to 2 * len
        if isHigh and src[i] >= p
            isFound := false

        if not isHigh and src[i] <= p
            isFound := false

    if isFound
        label.new(bar_index[len], p + _offset, tostring(p), style=_style, yloc=_yloc, color=_color)

if pvhl
    fun(high, lenH, true, label.style_labeldown, yloc.price, color.white, atrD)
    fun(low, lenL, false, label.style_labelup, yloc.price, color.white, -atrD)
Transfer Pivot Cache from a Pivot on one file to a Pivot on another?

Transfer Pivot Cache from a Pivot on one file to a Pivot on another?


By : Krishnakant Sreedhar
Date : March 29 2020, 07:55 AM
This might help you If your goal it to update another pivot table targeting the same data, then another way would be to create a new PivotCache pointing to the same source. This way, the targeted workbook will build the same PivotCache without the need to copy the DataTable, which is probably the cause of your memory issue.
Related Posts Related Posts :
  • How to create table via ODBC in ansi sql compliant way?
  • Postgres. How to get all the parents that meet the childs criteria?
  • 'Exploratory' SQL queries that uses one criteria to find more criteria
  • Adding Date columns
  • Display ContactName from Guid in Gridview using SQLDataSource / Asp.Net
  • making groups based on match between column 1 and column 2 from 1 table
  • Teradata 15.10 - Remove set of characters from a string
  • PostgreSQL array_agg(INTEGER[])
  • Does a query goes through all data when you only select the last N?
  • Displaying results based on customer first order after specific date SQL Server
  • Sum records and add note what was summed up in sql
  • Need help in generating values
  • RODBC - import a table
  • SQL: How to Group By task and choose only those that are completed?
  • PostgreSQL/PostGIS Count number of visits
  • How to I get a total count?
  • Many-to-many SQL relationship
  • Procedure call inside procedure
  • How to have decreasing running total in Oracle sql
  • SQL Group function query?
  • Which is the best way to calculate Year/Month in SQL Server?
  • Best way of sanitize unparametrizable sql
  • Querying for JSON data in Oracle creates syntax error
  • Show top 1 by max column
  • SQL: Insert newly created column in the same table
  • How to extract numbers after string using regexp?
  • 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)
  • 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
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org