logo
down
shadow

How to convert multiple rows to one row in SQL Server?


How to convert multiple rows to one row in SQL Server?

By : QuillStem
Date : November 22 2020, 10:33 AM
Hope this helps I have a problem with a query. Let's say I have two tables named PersonInfo and PersonEducation. I applied join operation on these tables with StudentId and I have a result like that. , Test Data
code :
DECLARE @TABLE TABLE (StudentIdId INT, Name VARCHAR(100), University VARCHAR(100)
                       , Department VARCHAR(100),[Status] VARCHAR(100))
INSERT INTO @TABLE VALUES 
(1 ,'John','Cambridge','Computer'  ,'Graduated'),
(1 ,'John','Berkeley' ,'Mathematic','Graduated'),
(1 ,'John','Boston'   ,'Economy'   ,'Ongoing'),
(2 ,'Pete','Cambridge','Computer'  ,'Graduated'),
(2 ,'Pete','Berkeley' ,'Mathematic','Graduated')
SELECT t.StudentIdId
      ,t.Name
      ,STUFF((SELECT ', ' + University 
              FROM @TABLE 
              WHERE StudentIdId = t.StudentIdId
              FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS University
      ,STUFF((SELECT ', ' + Department 
              FROM @TABLE 
              WHERE StudentIdId = t.StudentIdId
              FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS Department
      ,STUFF((SELECT ', ' + [Status] 
              FROM @TABLE 
              WHERE StudentIdId = t.StudentIdId
              FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS [Status]

FROM @TABLE t 
GROUP BY t.StudentIdId ,t.Name
╔═════════════╦══════╦═════════════════════════════╦═══════════════════════════════╦═══════════════════════════════╗
║ StudentIdId ║ Name ║         University          ║          Department           ║            Status             ║
╠═════════════╬══════╬═════════════════════════════╬═══════════════════════════════╬═══════════════════════════════╣
║           1 ║ John ║ Cambridge, Berkeley, Boston ║ Computer, Mathematic, Economy ║ Graduated, Graduated, Ongoing ║
║           2 ║ Pete ║ Cambridge, Berkeley         ║ Computer, Mathematic          ║ Graduated, Graduated          ║
╚═════════════╩══════╩═════════════════════════════╩═══════════════════════════════╩═══════════════════════════════╝


Share : facebook icon twitter icon
How to convert multiple rows into one row with multiple columns using Pivot in SQL Server when data having NULL values

How to convert multiple rows into one row with multiple columns using Pivot in SQL Server when data having NULL values


By : hanis15
Date : March 29 2020, 07:55 AM
Any of those help I have a table from which I want to get data under some conditions. I am getting data with below query. , Use a group by with SUM to get the desired output:
code :
    SELECT track, 
    SUM(ISNULL(ytd, 0)) AS [ytd], 
    SUM(ISNULL([5], 0)) AS [5],
    SUM(ISNULL([4], 0)) AS [4],
    SUM(ISNULL([3], 0)) AS [3],
    SUM(ISNULL([2], 0)) AS [2]
    FROM (SELECT track,ytd,weekno,[unit]
    FROM SMIrawdataFinal where album = 'XYZ' 
            AND unit IS NOT NULL
            AND tracktype='focus' 
            AND track='A')    as s PIVOT
    (SUM(unit) FOR weekno in ([5],[4],[3],[2]))AS pivot1
    GROUP BY track
    track   | ytd   | 5 | 4  | 3    | 2
    --------------------------------
    A       | 19    | 5 | 4  | 3    | 2
Convert Rows to Multiple columns in SQL Server

Convert Rows to Multiple columns in SQL Server


By : techjunk1e
Date : March 29 2020, 07:55 AM
should help you out Trying to Convert Rows to Multiple columns in SQL Server as show below: , If I understand correctly,
code :
select max(case when seqnum = 1 then portcode end) as portcode_1,
       max(case when seqnum = 1 then carcode end) as carcode_1,
       max(case when seqnum = 2 then portcode end) as portcode_2,
       max(case when seqnum = 2 then carcode end) as carcode_2,
       max(case when seqnum = 3 then portcode end) as portcode_3,
       max(case when seqnum = 3 then carcode end) as carcode_3       
from (select t.*, row_number() over (order by (select null)) as seqnum
      from t
     ) t;
SQL Server : convert multiple rows in single column

SQL Server : convert multiple rows in single column


By : Vcreator
Date : March 29 2020, 07:55 AM
this will help I am running into an issue with T-SQL code. There is a CSV file that I need to import and transform into a SQL Server table. The problem is that the CSV file is not correctly format and looks like this: , Example
code :
Declare @S varchar(max) = 'Recipe,Recipe,Recipe,Recipe
0,1,3,4
Data1,Data2,Data3,Data4'

;with cte as (
    Select CN=A.RetSeq
          ,RN=B.RetSeq
          ,Value=B.RetVal
     From  [dbo].[tvf-Str-Parse](@S,char(13)+char(10)) A
     Cross Apply [dbo].[tvf-Str-Parse](A.RetVal,',') B
)
Select Str = Stuff((Select ',' +Value From cte Where RN=A.RN Order By CN For XML Path ('')),1,1,'') 
 From (Select Distinct RN from cte) A
 Order By A.RN
Str
Recipe,0,Data1
Recipe,1,Data2
Recipe,3,Data3
Recipe,4,Data4
CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = row_number() over (order by 1/0)
          ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
Declare @S varchar(max) = 'Recipe,Recipe,Recipe,Recipe
0,1,3,4
Data1,Data2,Data3,Data4'

;with cte as (
    Select CN=A.RetSeq
          ,RN=B.RetSeq
          ,Value=B.RetVal
     From  (
            Select RetSeq = row_number() over (order by 1/0)
                  ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
            From  (Select x = Cast('<x>' + replace((Select replace(@S,char(13)+char(10),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
            Cross Apply x.nodes('x') AS B(i)
           ) A
     Cross Apply (
                    Select RetSeq = row_number() over (order by 1/0)
                          ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                    From  (Select x = Cast('<x>' + replace((Select replace(A.RetVal,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                    Cross Apply x.nodes('x') AS B(i)
                 )  B
)
Select Str = Stuff((Select ',' +Value From cte Where RN=A.RN Order By CN For XML Path ('')),1,1,'') 
 From (Select Distinct RN from cte) A
 Order By RN
Declare @S varchar(max) = 'Recipe,Recipe,Recipe,Recipe
1,,3,4
Data1,Data2,Data"3,Data4'


;with cte as (
Select CN    = A.[key]
      ,RN    = B.[Key]
      ,Value = replace(B.Value,'||','"')
 From OpenJSON('["'+replace(replace(@S,'"','||'),char(13)+char(10),'","')+'"]')  A
 Cross Apply ( 
               Select *
                From OpenJSON('["'+replace(A.Value,',','","')+'"]')  
             ) B
)
Select Str = Stuff((Select ',' +Value From cte Where RN=A.RN Order By CN For XML Path ('')),1,1,'') 
 From (Select Distinct RN from cte) A
 Order By RN
Str
Recipe,1,Data1
Recipe,,Data2     -- null (2 is missing
Recipe,3,Data"3   -- has double quote
Recipe,4,Data4
SQL Server - Convert one row into a key value multiple rows

SQL Server - Convert one row into a key value multiple rows


By : user3603949
Date : March 29 2020, 07:55 AM
will be helpful for those in need Use UNPIVOT.
code :
CREATE TABLE #MyTable
(
    Column1 INT
    , Column2 INT
    , Column3 INT
    , Column4 INT
)

INSERT INTO #MyTable VALUES
(1, 8, 3, 5)

SELECT *
FROM #MyTable
UNPIVOT
(
    [Columns] FOR [Column] IN (Column1, Column2, Column3, Column4)
) AS UP
Convert Single row to Multiple rows in SQL Server

Convert Single row to Multiple rows in SQL Server


By : ATUL JHARIYA
Date : March 29 2020, 07:55 AM
will be helpful for those in need I have table that looks like , Use union all:
Related Posts Related Posts :
  • 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
  • Finding max(date) from the resultant query output
  • updating multiple columns using case statement in sql server
  • Nested SQL query having select subquery in update statement
  • "putting" a certain object to the screen from inside an array
  • Retrieve segment from value
  • Error while creating a table in oracle 11g
  • Transaction with Sequelize doesn't work
  • Search time complexity of this sql query
  • How to return unique records between two tables without using distinct and union?
  • Is it possible to maximum occurences of a value for each key in my table all in one query?
  • Divide table into groups of 5000 based on rank
  • rolling back exec and sp_OA methods
  • How to use substring and charindex in google query language
  • Find table information where part of column matches char variable
  • Need to retrieve rows from table where the following condition was not satisfied column A = column B and column B = colu
  • Assigning an index to a constraint
  • Retrieve records from multiple Records returned by Sub-Query
  • SQL DELETE group of records based on opposite group being empty
  • SQL GetDate() returns wrong time
  • Total sales amount for customers in cities
  • SQL Server -- updating the `sys.*` tables and not just reading them
  • SQL Order By using concat
  • SQL server 2008 - subquery returned more than one value
  • Group Data Hourly and Insert Into a Summary Table in Postgres
  • Execute SQL Server Pass-Through Query From Access VBA
  • MS Access SQL Transform Aggregate Manipluation of Values for Pivot
  • SQL "if exists..." dynamic query
  • HANA: Split string?
  • Create a list of dates and match records to the list
  • Why won't SQL round correctly from my batch script?
  • SQLServer - Multiple PIVOT on same columns
  • Update a table foreign keys by substituting by a lookup from the result of another table
  • Deleting rows in a single database table based on the values of other rows
  • Search and replace part of string in database - what are the pitfalls?
  • How could I generate auto insert function SEAT_NO like A1,A2.... B1,B2 in sql server
  • whats a good way to optimize a table? SQL server 2k8
  • Sql server dynamic sequences
  • JPQL Query - object that's in one list and isn't in another
  • How to sort lines in select resquest to don't have multiple same lines
  • SQL Server : new and old value from history table in one row
  • SQL select bottom half
  • How to sum order cost
  • Repeatedly creating and dropping temp table of the same name
  • oracle yyyyiw glitch
  • ORDER BY id IN Subquery
  • SQL max concurrent sessions per hour of day
  • postgresql events per hours
  • database db2: Group by custom date range
  • How to allow letters only in VARCHAR2 column (Oracle SQL)
  • What is the equivalent of T-SQL ISNUMERIC function in HANA Sqlscript?
  • Send SQL Query and Wait For Response VB.NET
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org