SQL Server: How can I group multiple row values into separate columns?
Here is a dynamic pivot way:
declare @table table (Email varchar(64), Phone varchar(16), ID varchar(3))
insert into @table
values
('x@x.com','555-5555','001'),
('x@x.com','555-5556','001'),
('x@x.com','555-5557','001'),
('x@x.com','555-5558','001'),
('x@xdd.com','333-5556','002'),
('x@xdd.com','444-5556','002'),
('x@xdd.com','777-5556','002')
select
Email
,Phone
,ID
,row_number() over (partition by ID order by Phone) as RN
into #staging
from
@table
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(RN)
FROM (SELECT DISTINCT RN FROM #staging) AS RN
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT Email, ID, ' + @ColumnName + '
FROM #staging
PIVOT(MAX(Phone)
FOR RN IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
drop table #staging
If you are only expecting 3, as you stated, you can skip the dynamic...
declare @table table (Email varchar(64), Phone varchar(16), ID varchar(3))
insert into @table
values
('x@x.com','555-5555','001'),
('x@x.com','555-5556','001'),
('x@xdd.com','333-5556','002'),
('x@xdd.com','444-5556','002'),
('x@xdd.com','777-5556','002')
;with cte as(
select
Email
,Phone
,ID
,row_number() over (partition by ID order by Phone) as RN
from
@table)
select
Email
,max(case when RN = 1 then Phone end) as Phone1
,max(case when RN = 2 then Phone end) as Phone2
,max(case when RN = 3 then Phone end) as Phone3
,ID
from
cte
group by
Email
,ID
Group by column and multiple Rows into One Row multiple columns
As I mention in the comments, what you need here is a PIVOT or Cross tab; I prefer the latter so what I am going to use.
The non-dynamic solution to this would be as follows:
WITH RNs AS(
SELECT WorkOrder,
TestType,
Result,
ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column
FROM dbo.Result)
SELECT WorkOrder,
TestType,
MAX(CASE RN WHEN 1 THEN Result END) AS Result1,
MAX(CASE RN WHEN 2 THEN Result END) AS Result2,
MAX(CASE RN WHEN 3 THEN Result END) AS Result3
FROM RNs R
GROUP BY WorkOrder,
TestType;
The problem, however, is that this "locks" you into 3 results, but you suggest there is an indeterminate number of results. Therefore you need a dynamic solution.
The below will work up to 100 results. if you do need more columns than than, then add more CROSS JOINs to N in the CTE Tally. This results is something like this (which is quite messy).
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10),
@MaxTally int;
SELECT @MaxTally = MAX(C)
FROM (SELECT COUNT(*) AS C
FROM dbo.Result
GROUP BY WorkOrder,
TestType) R;
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2) --100 rows, add more Ns for more rows
SELECT @SQL = N'WITH RNs AS(' + @CRLF +
N' SELECT WorkOrder,' + @CRLF +
N' TestType,' + @CRLF +
N' Result,' + @CRLF +
N' ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column' + @CRLF +
N' FROM dbo.Result)' + @CRLF +
N'SELECT WorkOrder,' + @CRLF +
N' TestType,' + @CRLF +
--Using FOR XML PATH due to not knowing SQL Server version
STUFF((SELECT N',' + @CRLF +
CONCAT(N' MAX(CASE RN WHEN ',T.I,N' THEN Result END) AS Result',T.I)
FROM Tally T
ORDER BY T.I ASC
FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + @CRLF +
N'FROM RNs R' + @CRLF +
N'GROUP BY WorkOrder,' + @CRLF +
N' TestType;';
PRINT @SQL; --Your best friend.
EXEC sys.sp_executesql @SQL;
Convert multiple rows into single row with more columns
Several options here.
You may notice that I listed the 3 column in the subquery. You need to "feed" your PIVOT with only the required columns.
Known Columns to Pivot
Select *
From (
Select ID
,Prop
,Value
From YourTable
) src
Pivot (max(Value) for Prop in ([P1],[P2],[P3]) ) pvt
Dynamic Pivot and version <2017
Declare @SQL varchar(max) = '
Select *
From (
Select ID
,Prop
,Value
From YourTable
) A
Pivot (max([Value]) For [Prop] in (' + stuff((Select Distinct ','+QuoteName(Prop)
From YourTable
Order By 1
For XML Path('')),1,1,'') + ') ) p'
Exec(@SQL);
Dynamic Pivot for 2017+
Declare @SQL varchar(max) = '
Select *
From (
Select ID
,Prop
,Value
From YourTable
) A
Pivot (max([Value]) For [Prop] in (' + (Select string_agg(quotename(Prop),',')
From (Select distinct Prop From YourTable ) A) + ') ) p'
Exec(@SQL);
GROUP BY and create columns for each unique value grouped on (output as 1 row always), then use the output as a JOIN - SQL Server
You should group by BillingId only and use conditional aggregation:
SELECT b.BillingId,
SUM(CASE WHEN p.PaymentType = 'electronic' THEN p.PaymentAmount ELSE 0 END) AS AmountPaid,
SUM(CASE WHEN p.PaymentType = 'adjustment' THEN p.PaymentAmount ELSE 0 END) AS AmountAdjusted
FROM Billing AS b LEFT JOIN Payment AS p
ON b.BillingId = p.BillingId
GROUP BY b.BillingId;
See the demo.
Using group by on multiple columns
Group By X means put all those with the same value for X in the one group.
Group By X, Y means put all those with the same values for both X and Y in the one group.
To illustrate using an example, let's say we have the following table, to do with who is attending what subject at a university:
Table: Subject_Selection
+---------+----------+----------+
| Subject | Semester | Attendee |
+---------+----------+----------+
| ITB001 | 1 | John |
| ITB001 | 1 | Bob |
| ITB001 | 1 | Mickey |
| ITB001 | 2 | Jenny |
| ITB001 | 2 | James |
| MKB114 | 1 | John |
| MKB114 | 1 | Erica |
+---------+----------+----------+
When you use a group by on the subject column only; say:
select Subject, Count(*)
from Subject_Selection
group by Subject
You will get something like:
+---------+-------+
| Subject | Count |
+---------+-------+
| ITB001 | 5 |
| MKB114 | 2 |
+---------+-------+
...because there are 5 entries for ITB001, and 2 for MKB114
If we were to group by two columns:
select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester
we would get this:
+---------+----------+-------+
| Subject | Semester | Count |
+---------+----------+-------+
| ITB001 | 1 | 3 |
| ITB001 | 2 | 2 |
| MKB114 | 1 | 2 |
+---------+----------+-------+
This is because, when we group by two columns, it is saying "Group them so that all of those with the same Subject and Semester are in the same group, and then calculate all the aggregate functions (Count, Sum, Average, etc.) for each of those groups". In this example, this is demonstrated by the fact that, when we count them, there are three people doing ITB001 in semester 1, and two doing it in semester 2. Both of the people doing MKB114 are in semester 1, so there is no row for semester 2 (no data fits into the group "MKB114, Semester 2")
Hopefully that makes sense.
SQL Group Multiple Results from Single Table
Building on the answer from the previous link you can create a cte then execute the query
This will given you the
SELECT Main.UniqueID,
LEFT(Main.ItemTypes,Len(Main.ItemTypes)-1) As "ItemTypes"
FROM
(
SELECT DISTINCT ST2.UniqueID,
(
SELECT ST1.ItemType + ',' AS [text()]
FROM dbo.TheTable ST1
WHERE ST1.UniqueID = ST2.UniqueID
ORDER BY ST1.UniqueID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') ItemTypes
FROM dbo.TheTable ST2
) [Main]
Once you have that you can build this into a cte with the with statement then join back on the table to get the rest of the data.
with ItemTypes as
(
SELECT Main.UniqueID,
LEFT(Main.ItemTypes,Len(Main.ItemTypes)-1) As "ItemTypes"
FROM
(
SELECT DISTINCT ST2.UniqueID,
(
SELECT ST1.ItemType + ',' AS [text()]
FROM dbo.TheTable ST1
WHERE ST1.UniqueID = ST2.UniqueID
ORDER BY ST1.UniqueID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') ItemTypes
FROM dbo.TheTable ST2
) [Main]
)
Select Distinct TheTable.UniqueID, ItemTypes.ItemTypes, TheTable.OtherData
from TheTable join ItemTypes
on (TheTable.UniqueID = ItemTypes.UniqueID)
Results
UniqueID ItemTypes OtherData
--------- -------------------------- --------------------------------
1234 apples,oranges,grapes OtherData
2233 red fish,blue fish OtherData
There are a few expensive operations this will be an expensive query to run. but with 2million rows should be ok with a good server.
SQL Server : how to combine multiple rows based on two different columns?
This query depends on the value of term column and will work if the values are between 1 and 3. It's used to find continues ranges
declare @t table (Id int, Program char(1), Term int, Year int, GradTerm int, GradYear int)
insert into @t
values (1, 'P', 2, 2001, 3, 2005)
, (2, 'P', 3, 2001, 3, 2005), (3, 'P', 2, 2002, 3, 2005)
, (4, 'M', 2, 2002, 2, 2004), (5, 'M', 3, 2002, 2, 2004)
select
ID = row_number() over (order by grp), Program, startterm = min(Term)
, startyear = min(Year), EndTerm = max(Term), EndYear = max(Year), GradTerm, GradYear
from (
select
*, grp = Year * 3 + Term - row_number() over (partition by Program, GradTerm, GradYear order by Year, Term)
from
@t
) t
group by Program, GradTerm, GradYear, grp
Output:
ID Program startterm startyear EndTerm EndYear GradTerm GradYear
-----------------------------------------------------------------------
1 P 2 2001 3 2001 3 2005
2 P 2 2002 2 2002 3 2005
3 M 2 2002 3 2002 2 2004
EDIT:
SID should be in group by also you need to put it in row_number. Also, my query in the answer has some mistakes. And because of that you could have gotten wrong results. Here's a correct version
declare @t table (SID int, Program char(1), Term int, Year int, GradTerm int, GradYear int)
insert into @t
values (1, 'P', 2, 2001, 3, 2005)
, (1, 'P', 3, 2001, 3, 2005), (1, 'P', 2, 2002, 3, 2005)
, (2, 'M', 2, 2002, 2, 2004), (2, 'M', 3, 2002, 2, 2004)
select
SID, Program, startterm = right(min(val), 1)
, startyear = left(min(val), 4), EndTerm = right(max(val), 1)
, EndYear = left(max(val), 4), GradTerm, GradYear
from (
select
*, grp = Year * 3 + Term - row_number() over (partition by SID, Program, GradTerm, GradYear order by Year, Term)
, val = concat(Year, Term)
from
@t
) t
group by SID, Program, GradTerm, GradYear, grp
SQL: Combine multiple rows into a single row with more columns
This would be the ideal way to do it
SELECT *
FROM TableName
PIVOT(MAX([Feedback value])
FOR [Feedback type] IN ([Overall rating], [Duration], [Representative])
) AS PVTTable
However, user is not able to use pivot, so this will work as well
SELECT [ID],
MAX(CASE WHEN [Feedback type] = 'Overall rating'
THEN [Feedback value]
ELSE '' END) AS 'Overall Rating',
MAX(CASE WHEN [Feedback type] = 'Duration'
THEN [Feedback value]
ELSE '' END) AS 'Duration',
MAX(CASE WHEN [Feedback type] = 'Representative'
THEN [Feedback value]
ELSE '' END) AS 'Representative'
FROM TableName
GROUP BY [ID]
Related Topics
Parse a Date from Unformatted Text in SQL
What Does It Mean to Have Jobs with a Null Stop Date
How to Use a Returned Column Value as a Table Name in an SQLite Query
Access Query Counter Per Group
SQL Difference Between in and or in Where
How to Use % Operator from the Extension Pg_Trgm
Issue with To_Date Function with Sysdate
How to Find If a Value Exists Within a Varray
Trying to Split One Column to Multiple Columns Using Snowflake SQL
How to Get Specific Xml Namespace in Xquery in SQL Server
Ssis - Performing a Lookup on Another Table to Get Related Column
Like Operation Returns No Rows on Nvarchar Column Filter If the Column Data Start with Numeric
Duplicate Groups of Records to Fill Multiple Date Gaps in Google Bigquery
Insert Deleted Values into a Table Before Delete with a Delete Trigger
SQL Server: Self-Reference Fk, Trigger Instead of on Delete Cascade