Compress rows with nulls and duplicates into single rows
Select
ID,
AssnDate,
AssnTxt,
Max(IsNull(Sally,0)) AS Sally,
Max(IsNull(Ted, 0)) As Ted,
Max(IsNull(Bob, 0)) As Bob
From vwGrades
Group By
ID,
AssnDate,
AssnTxt
Select rows with same ID, collapse rows with null / non-null values
There is no such thing as a "first" row in a table, unless you have a column that specifies the ordering. Your table does not have such a column.
So, the best that you can do is aggregation:
select email, max(street) as street, max(town) as town, max(zip) as zip
from testdata
group by email;
SQL Pivot 2nd step -- Combine rows / Remove nulls
Aren't you missing some kind of grouping criterion? How do you determine, that A belongs to 123 and not to CDE? And why is randString in the first line and not in the second?
This is easy, with such a grouping key:
DECLARE @tbl TABLE(GroupingKey INT, ListNum VARCHAR(100),[Value] VARCHAR(100));
INSERT INTO @tbl VALUES
(1,'List1','A')
,(1,'List3','123')
,(2,'List3','CDE')
,(2,'List1','Somestring')
,(1,'List2','randString');
SELECT p.*
FROM @tbl
PIVOT
(
MAX([Value]) FOR ListNum IN(List1,List2,List3)
) p;
But with your data this seems rather random...
UPDATE: A random approach...
The following approach will sort the values into their columns rather randomly:
DECLARE @tbl TABLE(ListNum VARCHAR(100),[Value] VARCHAR(100));
INSERT INTO @tbl VALUES
('List1','A')
,('List3','123')
,('List3','CDE')
,('List1','Somestring')
,('List2','randString');
--This will use three independant, but numbered sets and join them:
WITH All1 AS (SELECT [Value],ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RandomNumber FROM @tbl WHERE ListNum='List1')
,All2 AS (SELECT [Value],ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RandomNumber FROM @tbl WHERE ListNum='List2')
,All3 AS (SELECT [Value],ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RandomNumber FROM @tbl WHERE ListNum='List3')
SELECT All1.[Value] AS List1
,All2.[Value] AS List2
,All3.[Value] AS List3
FROM All1
FULL OUTER JOIN All2 ON All1.RandomNumber=All2.RandomNumber
FULL OUTER JOIN All3 ON All1.RandomNumber=All3.RandomNumber ;
Hint: There is no implicit sort order in your table!
From your comment:
It’s simply the index / instance number. randString is the first non-null row.
Without a specific ORDER BY the same SELECT may return your data in any random order. So there is no first non-null row, at least not in the meaning of first comes before second...
Group rows with missing values in each row
A simple way do achieve it is to use subqueries on the select.
declare @MyTable table
(
FIELD1 varchar(50)
,FIELD2 varchar(50)
,FIELD3 varchar(50)
,FIELD4 varchar(50)
)
insert into @MyTable values
('TEST1', 'AAAAAA', NULL , NULL)
,('TEST1', NULL , 'BBBBBB', NULL)
,('TEST1', NULL , NULL , 'CCCC')
,('TEST2', 'XXXXXX', NULL , NULL)
,('TEST2', NULL ,'YYYYYY', NULL)
,('TEST2', NULL ,NULL ,'ZZZZ')
,('TEST3', 'UUUUUU','VVVVVV', NULL)
select * from @MyTable t0
select t1.FIELD1
, (select top 1 t2.FIELD2 from @MyTable t2 where t2.FIELD2 is not null and t2.FIELD1 = t1.Field1)
, (select top 1 t3.FIELD3 from @MyTable t3 where t3.FIELD3 is not null and t3.FIELD1 = t1.Field1)
, (select top 1 t4.FIELD4 from @MyTable t4 where t4.FIELD4 is not null and t4.FIELD1 = t1.Field1)
from @MyTable t1
group by t1.FIELD1
Combine rows ignoring null values
You can get your output by aggregating by the first 3 columns and taking the maximum of the service column:
SELECT sales_org,
material,
mgroup,
MAX(service) AS service
FROM table_name
GROUP BY
sales_org,
material,
mgroup;
Which, for the sample data:
CREATE TABLE table_name (sales_org, material, mgroup, service) AS
SELECT 'XS13', NULL, 10, 'ZSERV1' FROM DUAL UNION ALL
SELECT 'XS13', 'M1', 10, 'ZSERV1' FROM DUAL UNION ALL
SELECT 'XS13', NULL, 10, NULL FROM DUAL UNION ALL
SELECT 'XS13', NULL, 10, 'ZSERV2' FROM DUAL UNION ALL
SELECT 'XS13', NULL, 11, 'ZSERV2' FROM DUAL UNION ALL
SELECT 'XS13', 'M2', 12, 'ZSERV2' FROM DUAL UNION ALL
SELECT 'XS14', 'M2', NULL, NULL FROM DUAL;
Gives the output:
SALES_ORG MATERIAL MGROUP SERVICE XS13 null 10 ZSERV2 XS13 M1 10 ZSERV1 XS13 null 11 ZSERV2 XS13 M2 12 ZSERV2 XS14 M2 null null How to merge SQL rows with null values
Try this below SQL, Based on Gordon
Declare @Cols Varchar(Max)
select @Cols= coalesce(@Cols+',', '')+ ' max(' + column_name + ') as ' + column_name
from information_schema.columns
where table_name = 'tempHilltopWaterQualityExtractPivot' and Column_Name <> 'site'
Declare @Query Varchar(max)
SET @Query='Select Site, '+ @Cols+' From tempHilltopWaterQualityExtractPivot Group by Site'
EXEC(@Query)sql: remove multiple row null values, concathenate into one row
sllev got me on the right track. In my case, because all values except for one were always going to be null in any given column, i just had to use the MAX() function. my final sql statement looked like this:
Select MAX(Merge_Table_Parcel_1.txtFrequency1) AS Frequency1,
MAX(Merge_Table_Parcel_1.SUM_INST_N) AS SUM_INST_N1,
MAX(Merge_Table_Parcel_1.SUM_INST_D) AS SUM_INST_D1,
MAX(Merge_Table_Parcel_1.SUM_CTRN_D) AS SUM_CTRN_D1,
MAX(Merge_Table_Parcel_1.SUM_CTRN_N) AS SUM_CTRN_N1,
MAX(Merge_Table_Parcel_1.SUM_TRAN_N) AS SUM_TRAN_N1,
MAX(Merge_Table_Parcel_1.SUM_PPU) AS SUM_PPU1,
MAX(Merge_Table_Parcel_1.SUM_PPUJOBS) AS SUM_PPUJOBS1,
MAX(Merge_Table_Parcel_1.SUM_DAYT) AS SUM_DAYT1,
MAX(Merge_Table_Parcel_1.SUM_RESD_D) AS SUM_RESD_D1,
MAX(Merge_Table_Parcel_1.SUM_RESD_N) AS SUM_RESD_N1,
MAX(Merge_Table_Parcel_1.SUM_ON_STRN_D) AS SUM_ON_STRN_D1,
MAX(Merge_Table_Parcel_1.SUM_ON_STRN_N) AS SUM_ON_STRN_N1
FROM Merge_Table_Parcel_1Simple, i know, but i dident see it right away. Thank you to everyone who contributed a response, and to sllev who pointed me in the right direction.
Related Topics
Teradata, Reset When, Partition By, Order By
A Select Query Selecting a Select Statement
Select All Records Don't Meet Certain Conditions in a Joined Table
Delete Statement in SQL Is Very Slow
Pivot with Dynamic Columns in Oracle
How to Generate a Random, Unique, Alphanumeric Id of Length N in Postgres 9.6+
How to Avoid "Table Mutating" Errors
How to Use Merge on Linked Servers
Postgresql Visual Interface Similar to PHPmyadmin
Differencebetween a Candidate Key and a Primary Key
Find Groups with Matching Rows
Db2 - Returning the Top 5 of Each Category
Pass String Variable Without Quotes in Query Vba
How to Delete Duplicates from a Database Table Based on a Certain Field