logo
down
shadow

TSQL turn table rows into columns with column name and value coming from a row


TSQL turn table rows into columns with column name and value coming from a row

By : Sunny Sourabh
Date : November 22 2020, 02:59 PM
around this issue You probably want to try a Dynamic Pivot Query. This will allow you to build the columns based on the type values in your second table.
There are several ways to get the column names into the format you'll need for the pivot query.. this is one way.
code :
DECLARE @columns VARCHAR(MAX)
SELECT  @columns = COALESCE(@columns + ',','') + QUOTENAME(type)
FROM    (SELECT DISTINCT [type] FROM secondTable) st
ORDER BY st.[type]
DECLARE @sql NVARCHAR(MAX)  
SET @sql = 
    'SELECT * FROM (
        SELECT ft.ID,
            ft.Col1,
            ft.Col2,
            ft.Col3,
            st.type,
            st.value
         FROM 
            firstTable ft
            JOIN secondTable st ON ft.ID = st.ID
    ) t
    PIVOT
    (
        MAX(Value)
        FOR type in (' + @columns + ')
    ) p
'
EXECUTE sp_executesql @sql


Share : facebook icon twitter icon
TSQL: Join columns, but rows in one column have multiple values

TSQL: Join columns, but rows in one column have multiple values


By : dwitte
Date : March 29 2020, 07:55 AM
should help you out I have a query where I have to join two tables. Lets say T1 and T2. T1 has a column with an Id. And every row has only one Id value. However, the second table is where i'm struggling. in T2 there is a column with the Id's but it can be possible that one row has multiple of those Id's. So as example T1.col1 has this value: 737382. But T2.col2 can have this entries in one row: 737382;239112;2838210;9923834;2388342;... , try
code :
    select tab2.*   -- whatever
      from t1 tab1
inner join t2 tab2 on ( ';'||tab2.col2||';' like '%;'||tab1.col1||';%' )
         ;
TSQL Parse single column into multiple columns/rows without a function

TSQL Parse single column into multiple columns/rows without a function


By : user2567690
Date : March 29 2020, 07:55 AM
help you fix your problem I'm trying to query a ticketing system, where multiple users can be assigned to a ticket at once.
code :
create table Comma(commaValue varchar(50))
go
insert into Comma Values('429173,525304,390497')
insert into Comma Values('429173,525304,390497')

;WITH Split_Requests (xmlname)
AS
(
    SELECT 
    CONVERT(XML,'<Requests><Request>'  
    + REPLACE(commaValue,',', '</Request><Request>') + '</Request></Requests>') AS xmlname
      FROM Comma
)

SELECT 
xmlname.value('/Requests[1]/Request[1]','varchar(25)') AS [Type1],
xmlname.value('/Requests[1]/Request[2]','varchar(25)') AS [Type2],
xmlname.value('/Requests[1]/Request[3]','varchar(25)') AS [Type3]
FROM Split_Requests
Tsql transpose rows to columns, grouping by a column

Tsql transpose rows to columns, grouping by a column


By : MasterSchema
Date : March 29 2020, 07:55 AM
Hope that helps I've this set of data: , The following will do it:
code :
DECLARE @pivotFields varchar(255) =
    STUFF(
        (
            SELECT ', [' + item + ']'
            FROM
                (
                    SELECT DISTINCT item
                    FROM YourTable
                ) Q
            ORDER BY item
            FOR XML PATH ('')
        )
        , 1, 2, ''
    )

DECLARE @sql varchar(1000) =
    '
        SELECT cId, ' + @pivotFields + '
        FROM
            (
                SELECT
                    cId
                    , item
                    , value
                FROM YourTable
            ) Q
            PIVOT
            (
                MAX(value)
                FOR item IN (' + @pivotFields + ')
            ) P
    '

EXEC (@sql)
Rotate rows into columns with column names not coming from the row

Rotate rows into columns with column names not coming from the row


By : Erick
Date : March 29 2020, 07:55 AM
wish help you to fix your issue And if you want to use PIVOT then the following works with the data provided:
code :
declare @Orders table (RowNo int, Id int, OrderNo int)

insert into @Orders (RowNo, Id, OrderNo)
  select 1, 101, 1 union all select 2, 101, 10

select Id, [1] OrderNo_0, [2] OrderNo_1
from (
  select RowNo, Id, OrderNo
 from @Orders
) SourceTable
pivot (
  sum(OrderNo)
  for RowNo in ([1],[2])
) as PivotTable
Turn the table columns as rows

Turn the table columns as rows


By : user2340173
Date : March 29 2020, 07:55 AM
To fix the issue you can do I have the table with the following structure. I need to turn columns as rows , Try This : Using unpivot
code :
with tbl as (
 select  1 as id, 'macbook' as name, 2 as "2011", 241 as "2012" from dual union
 select  2 as id, 'macbook' as name, 77 as "2011", 214 as "2012" from dual union
 select  3 as id, 'macbook' as name, 0 as "2011", 400 as "2012" from dual union
 select  4 as id, 'iphone' as name, 442 as "2011", 555 as "2012" from dual
)
select * from tbl
UNPIVOT (SALES FOR "DATE" IN ("2011" AS '2011', "2012" AS '2012'));
Related Posts Related Posts :
  • How can I iterate through characters in a SQL result value?
  • Write data of one column to unix file with data in another column of the same record as file name
  • How to select the first row of each group?
  • XML Schema totalDigits/fractionDigits vs. SQL precision/scale
  • There is an issue with my code here, can someone look?
  • MIN MAX query with a twist
  • join multiple table
  • PHP SQL Select between 4 columns
  • Hierarchical Query / base on table input
  • SQLite regular Expressions regex get exact word by number
  • invalid Column Name
  • Query for last 8 week data
  • Left Join not matching where data exists - Temp Tabls SQL 2012
  • Checking if a value is IN a colon separated column with in a table
  • PostgreSQL constraint error
  • How to use 2 instances of the same table
  • How to format date column to get the day name in Oracle SQL?
  • Run a query based on output of another query
  • How to find particular dates between two rows which have dates in oracle?
  • Oracle SQL: return only max value
  • How to use GROUP BY command properly in Google Big Query?
  • How to echo special characters in batch file?
  • How to read JSON data from SQL Server?
  • Oracle SQL: Use IN operator with inline array
  • Set Hex Value using prepeared stmt in java
  • SonarQube 5.2 Upgrade failed
  • searching for microsoft sql server equivalent of postgresql form
  • How can I write SQL in Oracle in my case?
  • Retrieve records from same name table from multiple databases in single result set
  • Replace duplicate values only in consecutive records with NULL
  • NOT vs <> Operator in sql server
  • Concatenate calculated fields in SQL
  • For each unique value of some groupid column, how do I get rows with last 3 dates?
  • SQL CASE in JOIN returning extra rows
  • Need to find only three different values in multiple columns then sum using Oracle
  • sql - Find the ID of the most booked flight
  • SQL insert data dynamic column name from another table
  • Insert random data in Oracle table
  • Regex to split values in PostgreSQL
  • Which databases are used within a SQL maintenance plan?
  • lag that works for each group separately
  • SparkSQL: conditional sum using two columns
  • SQL Server Maintenance plan history check for success or failure
  • aggregate function in recursive SQL
  • Why isn't row level security enabled for Postgres views?
  • sql - rental shop diagram
  • Open the table by clicking on the name of the query
  • How to tweak index_scan cost in postgres?
  • Confusion on SQLite Tables & Virtual Tables
  • Insert decimalplaces into number and the round the sum
  • Query to get a running total of an "inventory change" column
  • Calculate percentages on ranges
  • is there any app to read SQL table and make or convert to .NET class (Repository class, Model class, ...)
  • SQL(ite) filtering one-to-many tables
  • Mapping Null Values based on condition from two tables in SQL Server
  • Constraint on table to limit number of records to be stored
  • How to retrieve data between from date to system date using excel VBA (Automation Error)
  • SQL Insert Row of Data
  • Copying Data From One Table to a new table that is all foreign keys
  • Calling multi-statement TVF with different parameters in separate CTEs showing wrong results
  • shadow
    Privacy Policy - Terms - Contact Us © animezone.co