logo
down
shadow

SQL : Output multiple left joined rows as one record for csv file


SQL : Output multiple left joined rows as one record for csv file

By : Heinrich
Date : October 20 2020, 08:10 PM
around this issue If you have a fixed number of equipment for stock (I assume 3 for this )
You can do a pivot using GROUP BY.
code :
WITH tmpResult as (
    SELECT 
         [Stock ID],
         'Equipment ID ' + CAST(rn AS VARCHAR(16)) as lblEq,
         [Equipment ID],
         [Equipment Qty]
    FROM ( SELECT *, row_number() over (partition by [Stock ID] ORDER BY [Equipment ID]) as rn
           FROM LinkStockEquipment ) as rows
)    
SELECT [Stock ID], 
       MAX( CASE WHEN lblEq = 'Equipment ID 1' THEN [Equipment ID]  END) as [Eqp ID 1],
       MAX( CASE WHEN lblEq = 'Equipment ID 1' THEN [Equipment Qty] END) as [Eqp Qty 1],
       MAX( CASE WHEN lblEq = 'Equipment ID 2' THEN [Equipment ID]  END) as [Eqp ID 2],
       MAX( CASE WHEN lblEq = 'Equipment ID 2' THEN [Equipment Qty] END) as [Eqp Qty 2],
       MAX( CASE WHEN lblEq = 'Equipment ID 3' THEN [Equipment ID]  END) as [Eqp ID 3],
       MAX( CASE WHEN lblEq = 'Equipment ID 3' THEN [Equipment Qty] END) as [Eqp Qty 3]
FROM       tmpResult
GROUP BY [Stock ID];
WITH tmpResult as (
    SELECT 
         [Stock ID],
         'Eqp ID ' + CAST(rn AS VARCHAR(16)) as label,
         [Equipment ID] as [Value]
    FROM ( SELECT *, row_number() over (partition by [Stock ID] ORDER BY [Equipment ID]) as rn
           FROM LinkStockEquipment ) as rows

    UNION ALL

    SELECT 
         [Stock ID],
         'Eqp Qty ' + CAST(rn AS VARCHAR(16)) as label,
         CAST([Equipment Qty] AS VARCHAR(16))  as [Value]
    FROM ( SELECT *, row_number() over (partition by [Stock ID] ORDER BY [Equipment ID]) as rn
           FROM LinkStockEquipment ) as rows

)    
SELECT [Stock ID], 
       [Eqp ID 1], [Eqp Qty 1],
       [Eqp ID 2], [Eqp Qty 2]       
FROM   ( SELECT * FROM tmpResult ) as x
PIVOT ( 
        max( [Value] ) 
        for label in  ( [Eqp ID 1], [Eqp Qty 1], [Eqp ID 2], [Eqp Qty 2] )
      ) as pvt 
SELECT 
     [Stock ID],
     [label],
     [Value]
INTO tmpResult
FROM (
    SELECT 
         [Stock ID],
         'Eqp ID ' + CAST(rn AS VARCHAR(16)) as label,
         [Equipment ID] as [Value]
    FROM ( SELECT *, row_number() over (partition by [Stock ID] ORDER BY [Equipment ID]) as rn
           FROM LinkStockEquipment ) as rows

    UNION ALL

    SELECT 
         [Stock ID],
         'Eqp Qty ' + CAST(rn AS VARCHAR(16)) as label,
         CAST([Equipment Qty] AS VARCHAR(16))  as [Value]
    FROM ( SELECT *, row_number() over (partition by [Stock ID] ORDER BY [Equipment ID]) as rn
           FROM LinkStockEquipment ) as rows
    ) as x;
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.label)
            FROM tmpResult c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SELECT @cols; 

set @query = 'SELECT [Stock ID], ' + @cols + ' FROM  
            (
                SELECT *
                FROM tmpResult
           ) x
            pivot 
            (
                 max(Value)
                for label in (' + @cols + ')
            ) p '

execute(@query);


Share : facebook icon twitter icon
inner join on a left join not returning record when the left joined record is not present

inner join on a left join not returning record when the left joined record is not present


By : El Magico
Date : March 29 2020, 07:55 AM
will be helpful for those in need In SQL (and MySQL), the inner join, left outer join, right outer join, and cross join operations all have the same precedence. So, a left join b inner join c is (a left join b) inner join c. In the same way, the arithmetic expression 1 - 2 + 3 is (1 - 2) + 3 and not 1 - (2 + 3).
With this in mind, let's look at your from clause:
code :
from Tbl_1 as t1
left join Tbl_2 as t2 on t2.id = t1.id
inner join Tbl_3 as t3 on t2.tbl_3_id = t3.id
inner join Tbl_4 as t4 on t2.tbl_4_id = t4.id
left join Tbl_5 as t5 on t5.id = t2.id
inner join Tbl_6 as t6 on t2.tbl_6_id = t6.id
inner join Tbl_3 as t3 on t2.tbl_3_id = t3.id or t2.id is null
MySQL return record where left joined table has no record

MySQL return record where left joined table has no record


By : McCann Bell
Date : March 29 2020, 07:55 AM
this will help LEFT JOIN will include entries from the left table if there's no corresponding entry in the right tables.
However in your case you then filter by a.default = 1 which remove entries with no default address.
code :
LEFT JOIN (
  SELECT * FROM user_adresses
  WHERE `default` = 1
) a
ON a.user = u.id
LEFT JOIN user_addresses a
  ON a.user = u.id and a.default = 1
How does one craft an array from LEFT JOINs when joined tables have multiple matches and GROUP BY omits needed rows

How does one craft an array from LEFT JOINs when joined tables have multiple matches and GROUP BY omits needed rows


By : Tech fun
Date : March 29 2020, 07:55 AM
will be helpful for those in need I would select the first as you are currently, add a count(pe.post_id) to the query and if that number is greater than 1, theuy just query that table directly
If > 50% have more than 1 then always use the second query and never check for it.
code :
//do main query
foreach($post as &$p) {
   $elements = array();
   $p['elements'] = getElements($p['postid'])
}
Left joined combined with inner join not returning all the rows from the left table

Left joined combined with inner join not returning all the rows from the left table


By : Neema E
Date : March 29 2020, 07:55 AM
it fixes the issue When you mix inner and left joins the result is not a left join.
Also since you want all the rows of the table parts make it the left table:
code :
select o.ordernumber, p.PartDescription, p.partNumber
from parts p
left join inventory i on p.partid = i.partid 
left join orderdetail od on p.partID = od.partID
left join orders o on o.orderID = od.orderID
SQL Joined Tables - Multiple rows on joined table per 'on' matched field merged into one row?

SQL Joined Tables - Multiple rows on joined table per 'on' matched field merged into one row?


By : user3616570
Date : March 29 2020, 07:55 AM
like below fixes the issue To pivot your resultset over a fixed number of columns, you can use row_number() and conditional aggregation:
code :
select
    job_number, 
    total_amount,
    max(case when rn = 1 then charge_code end) charge_code1,
    max(case when rn = 1 then charge_amount end) charge_amount1,
    max(case when rn = 2 then charge_code end) charge_code2,
    max(case when rn = 2 then charge_amount end) charge_amount2,
    max(case when rn = 3 then charge_code end) charge_code3,
    max(case when rn = 3 then charge_amount end) charge_amount3 
from (
    select
        j.job_number,
        j.total_amount,
        c.charge_code,
        c.charge_amount,
        row_number() over(partition by job_number, total_amount order by c.charge_code) rn
    from DB.Jobs j
    inner join DB.Job_Charges c on j.job_number = c.job_number
) t
group by job_number, total_amount
Related Posts Related Posts :
  • T-SQL query with aggregates returning a non empty result set when predicate is false
  • Difference between top 2 over multiple rows
  • SQL Server 2016 SSIS Conditionally Overwrite Text File
  • The column was specified multiple times
  • is there something faster than Enumerable.Except<TSource> Method?
  • I need to take database back up database name with date
  • Numeric to money conversion fails for bigger value when using (DT_CY) type cast in derived column component
  • SQL server stored procedure insert with dynamic exec
  • Select * from x where column is quarter
  • How to grant unsafe assembly to a username found dynamically?
  • How can I loop though columns by name in an SSIS Script component?
  • SSRS Export to Excel Filter To Many Tabs
  • SQL Server 2012 Management Studio hangs
  • Can NOLOCK cause DISTINCT to fail?
  • SSRS Mobile Reports Distinct Count
  • Append api data to Amazon AWS SQL Server database
  • Merge columns together from multiples tables, columns not in order
  • Why Can't I See SQL Server Compact After I Install It?
  • SQL Server - Where Like statement with variable returning no results
  • SQL query to find non-matching fields in certain rows
  • SQL Server - Select between 2 dates of type DD/MM/YYYY
  • SQL Geography Datatype , checking Point in Polygon , STIntersects producing wrong result
  • A positional parameter cannot be found that accepts argument
  • Array var in SQL Server 2008
  • Using a variable in a sqsh script is not working while querying in sql server
  • Error Executing Query String
  • SQL view with substring in WHERE clause?
  • Specify Xml index storage location in Sql Server
  • How to push Spark dataframe to Sql Server table when set_identity is off?
  • Create index error -The object name contains more than the maximum number of prefixes. The maximum is 2
  • T-SQL: Minimum number of characters to remove/replace so that one string is substring of another
  • sql query that gets the difference between 2 recent rows for every row item that occurs more than once in a table
  • SSIS conditionally import if source is available
  • How to set SQL user password when deploying database project using CI/CD pipeline
  • SQL: How can I Parse firstname, lastname and title from fullname?
  • Should I store XML declaration in database and return using WebApi
  • SSIS Foreach loop error on CSV files
  • Azure SQL as Publisher
  • SSRS 2016 Mobile Reports Error with missing dataset
  • SQL 2005: Optimize upsert-like Stored Procedure using Cursor, possible?
  • SQLSERVER - How to list Procs created by a particular user?
  • SSIS Expression to get last day of the month given YYYYMM
  • how to sum up all time values on a given date?
  • Text Clause Explanation
  • SQL Server 2016 - Can't to log into SSMS after install
  • SQL Server table with different case in PowerShell SMO vs SSMS
  • Transpose and Create Variables
  • Removing Date From Date/Time Column
  • Subquery a Cast Function
  • Set based stacking update query
  • Can someone explain the `BypassPrepare` connection-parameter used when building an ETL via SSIS and VS?
  • What is the optimal way to get only latest ID's from table in SQL
  • How to join JSON with table?
  • Transforming a sql table into its inverse
  • Azure SQL Database Pricing
  • Log Reader error while adding a node in SQL Server peer to peer replication
  • Correct way to convert Month Name and Year to complete date SQL Server
  • Select records that appear twice, with a slight difference
  • tsql display value on first row of each group
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org