logo
Tags down

shadow

Multiple rows into columns with column values group by columns in SQL Server


By : anirudhvee
Date : October 18 2020, 06:10 PM
fixed the issue. Will look into that further If you have a column that specifies the ordering, you can use conditional aggregation:
code :
select max(case when columnname = 'SupplierGSTin' then value end) as SupplierGSTin,
       max(case when columnname = 'DocumentNumber' then value end) as DocumentNumber,
       max(case when columnname = 'SupplyType' then value end) as SupplyType
from (select t.*,
             row_number() over (partition by columnname order by ?) as seqnum
      from t
     ) t
group by rownum;


Share : facebook icon twitter icon

Group by multiple columns, get group total count and specific column from last two rows in each group


By : user56946
Date : March 29 2020, 07:55 AM
around this issue I have an SQL Server table with the following columns: , I would attempt this by using the following WITH clause:
code :
WITH RUL AS (
select
  UserId,
  Area,
  Action,
  ObjectId,
  RelatedUserLink as RelatedUserLink1,

  LAG(RelatedUserLink) OVER (PARTITION BY UserId, Area, Action, ObjectId ORDER BY Created) as RelatedUserLink2,

  ROW_NUMBER() OVER (PARTITION BY UserId, Area, Action, ObjectId ORDER BY Created DESC) latest_to_earliest,

  MAX(Created) OVER (PARTITION BY UserId, Area, Action, ObjectId) as Created,

  COUNT(*) OVER OVER (PARTITION BY UserId, Area, Action, ObjectId) as Count

from
  Notification
where UserId = 10
)
select 
  UserId,
  Area,
  Action,
  ObjectId,
  RelatedUserLink1,
  RelatedUserLink2,
  Created,
  Count
from 
  RUL 
where 
  latest_to_earliest = 1;

SQL Server Group rows with multiple occurences of Group BY columns


By : AKG
Date : March 29 2020, 07:55 AM
help you fix your problem As JNevill pointed out, this is a classic Gaps and Islands problem. Below is one solution using Row_Number().
code :
Select FirstName
      ,LastName
      ,StartDate=min(StartDate)
      ,EndDate  =max(EndDate)
 From (
        Select *
              ,Grp = Row_Number() over (Order by ID) - Row_Number() over (Partition By FirstName,LastName Order by EndDate)
         From  Temp
      ) A
 Group By FirstName,LastName,Grp
 Order By min(StartDate)

How to find 2 largest values from group of rows in multiple columns in Python and also show its row and column index at


By : Jonathan
Date : March 29 2020, 07:55 AM
To fix the issue you can do If there are only 3 or few groups I suggest create dictionary for mapping:
code :
d = {1:'FX', 2:'FY', 3:'FZ'}

for i in range (1,a+1):
    print (d[i]) 
    print (f'{a} Largest Values from {d[i]}')
    print (df1.loc[i])

How to find 2 largest values from group of rows in multiple columns in Python and also show its row and column index wit


By : user2797150
Date : March 29 2020, 07:55 AM
Any of those help I am new to Python. I want to find the largest values from all the columns for repetitive row elements (i.e. 5 to 150), and also show its row and column index label in output.The largest values should be absolute. (Irrespective of + or - sign). The group of row indexes will keep on repeating for 'n'number of times. For every 'nth' group of row index, I want 'n' largest values and their index positions for each group. Also if the largest value of a certain index is repeated in different groups, then program must allocate that index to the group having largest value of the two. There should not be duplicates for row indexes in different groups. , You may need two groupby filter
code :
df = df.reset_index()
df['key']=df['index'].eq(5).cumsum()
s=df.melt(['key','index'])
s=s[(-s.value.abs()).groupby(s['index']).rank()==1]
# just need select max row of each index before pick two from the each group
s[(-s.value.abs()).groupby(s.key).rank()<=2].sort_values('key')
Out[672]: 
    key  index variable  value
21    1      5   10_deg     47
44    1     12   20_deg    -56
11    2    130    0_deg     41
55    2    150   20_deg    -40
38    3    101   10_deg    -71
78    3     10   30_deg     63

How can I SELECT rows with multiple columns where a column includes different values and GROUP BY date?


By : Lasamba
Date : March 29 2020, 07:55 AM
Hope that helps I´ve found a solution using SELECT DISTINCT and UNION to get the expected result. I share it so that it can may help others, too:
code :
SELECT DISTINCT DATE(pr.created) AS created, pr.campaign_id AS campaign, pr.promotion AS promotion, pr.product_id AS product
FROM department AS dept
LEFT JOIN department_campaign AS dc ON dc.department_id = dept.id
LEFT JOIN department_products AS dp ON dp.department_id = dept.id
LEFT JOIN promotion_result AS pr ON pr.product_id = dp.product_id
WHERE dept.id = 2 AND pr.campaign_id = 1
UNION
SELECT DISTINCT DATE(pr.created) AS created, pr.campaign_id AS campaign, NULL, pr.product_id AS product
FROM department AS dept
LEFT JOIN department_campaign AS dc ON dc.department_id = dept.id
LEFT JOIN department_products AS dp ON dp.department_id = dept.id
LEFT JOIN promotion_result AS pr ON dc.campaign_id = pr.campaign_id
WHERE dept.id = 2 AND pr.campaign_id = 1 AND pr.product_id is NULL AND DATE(pr.created) NOT IN (SELECT DISTINCT DATE(pr1.created)
FROM department AS dept1
LEFT JOIN department_campaign AS dc1 ON dc1.department_id = dept1.id
LEFT JOIN department_products AS dp1 ON dp1.department_id = dept1.id
LEFT JOIN promotion_result AS pr1 ON pr1.product_id = dp1.product_id
WHERE dept1.id = 2 AND pr1.campaign_id = 1)
ORDER BY created ASC, promotion ASC
Related Posts Related Posts :
  • Improve performance of multiple insert and update queries
  • Column as a subquery
  • How to schedule a google-bigQuery so its results would import to spreadsheet on a daily basis?
  • ALTER TABLE takes forever even for a very little table
  • how to convert/calculate nvarchar to int
  • Spooky query behavior : Same query, different results when inside a view
  • Converting varchar to numeric nightmare
  • How to exclude certain combination pattern in data set?
  • Get results with wildcard
  • How to only select the SQL row with the MAX id in this join?
  • Converting a String with Length of Time into Different DataType
  • what does square brackets in an sql update statement mean?
  • CASE on WHERE CLAUSE SQL
  • Replacing value inside column
  • Weekly Schedule with SQL
  • How to choose minimum value row in Group By sql
  • Creating empty integer/decimal column by using Select
  • SQL string driving me crazy
  • summarize dates from a list of dates
  • Last 10 weeks in SQL Server
  • How to store less than / greater than in database
  • find number that is not 0 in a varchar sequence like 0000000001 or 00000123131
  • SQL query to fill multiple needs with partial quantities by using Oracle SQL window functions
  • SQL Server line break for CSV
  • I need to select clients who have multiple records in one column
  • How to group database column?
  • Getting a specific output from 2 tables
  • How to query different tables for data in order
  • About subquery in T-SQL
  • Memory Buffer pool taken by a Table
  • SQL : ORA-00906: missing left parenthesis
  • Data Conversion error while inserting into a new table from a joint statement
  • How to do sql pivot with multiple values in single column
  • Data of previous quarters based on sysdate
  • Rearrange Date filter in stored procedure
  • ORA-00942: table or view does not exist .or We are not able to drop
  • SQL Where date1 is 3 days bigger than date2
  • #1235 - This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' in MySQL
  • Can I SELECT *, COUNT(*) in the same query?
  • PLS-00642: local collection types not allowed in SQL statements. i am using oracle datadase 12c
  • How can I convert an integer representing EPOCH time to a timestamp in Athena (Presto)?
  • Find the One Random Set_id from each unique Skill_id
  • SQL ORACLE ORA-00923: FROM keyword not found where expected
  • implement a manual increment with just simple SQL
  • SYSDATETIMEOFFSET() to turn off the offset
  • If I query from V$SQL view, does it include SQL that are still running and not yet finished?
  • Is there a way to join results from two queries in Python using sqlalchemy engine?
  • What is the query to choose the number of people living at each address?
  • Filter By date with format 'dd/mm'
  • Listagg in query having aliases
  • SQL stored procedure error about int while there is no int
  • Selecting columns from string
  • Select * parent records that have their children meet some criteria
  • Identity specification
  • Excel: Execute SQL Procedure with VBA -- allow user to continue working -- do not hang the Excel Application
  • sql - single line per distinct values in a given column
  • Select from a select result using lag
  • Window analytic function where window frame and order are different fields?
  • How can i output table data (multiple rows) as a multiple rows of concatenated strings
  • SQL Replace Function Replacing Too Many Parts Of A String
  • shadow
    Privacy Policy - Terms - Contact Us © 35dp-dentalpractice.co.uk