Summarize or not summarize the result set based on grouping of row
probably sql makes me dizzy when complexity level increases. It is easier to put a for loop and work in c#.
I have a query like
select.field1,.field2, field3,field4
from table1
Suppose this returns rows 1, 2, 3, 4, 5, 6.
I want to return summarized one row if this result has same field2 and field3. if ANY of the rows is different then return all the rows.
Thanks in advance.
Here is Sample data. In this lis row number 1 and row 4 are parent items and others child items. When Summarizing, row 1 is summarized with all the child items but row number 4 is not summarized with children rows since row number 6 is has a different value field 2.
F开发者_StackOverflow中文版ield1 Field2 Field3 Field4(parentid)
1 paper cash null
2 Paper cash 1
3 paper cash 1
4 paper cash null
5 paper cash 4
6 pen cash 4
Here I want to return
field1 Field2 Field3 field4(all the child's id)
1 paper cash (2,3)
4 paper cash null
5 paper cash null
6 pen cash null
Hope this is better.
With SQL Server you'll have to create a function to assist with the concatenation and a #temp table to assist with post-processing (to avoid repeated calls to the function across the whole source table). If you move to a database platform released in the last 10 years, you'll have much more efficient solutions at your fingertips. :-)
Setup:
USE tempdb;
GO
CREATE TABLE dbo.[Sample]
(
Field1 INT,
Field2 VARCHAR(32),
Field3 VARCHAR(32),
Field4 INT
);
INSERT dbo.[Sample] SELECT 1,'paper','cash', NULL
UNION ALL SELECT 2,'Paper','cash', 1
UNION ALL SELECT 3,'paper','cash', 1
UNION ALL SELECT 4,'paper','cash', NULL
UNION ALL SELECT 5,'paper','cash', 4
UNION ALL SELECT 6,'pen', 'cash', 4;
GO
Function:
CREATE FUNCTION dbo.ConcatIDs
(
@Field1 INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s VARCHAR(8000);
SELECT @s = COALESCE(@s + ',', '')
+ CONVERT(VARCHAR(12), Field1)
FROM dbo.[Sample] AS s
WHERE Field4 = @Field1
AND NOT EXISTS
(
SELECT 1
FROM dbo.[Sample]
WHERE Field4 = s.Field4
AND Field1 <> s.Field1
AND (Field2 <> s.Field2 OR Field3 <> s.Field3)
);
RETURN @s;
END
GO
The query:
SELECT Field1, Field2, Field3, Field4, f4 = dbo.ConcatIDs(Field1)
INTO #x
FROM dbo.[Sample];
SELECT Field1, Field2, Field3,
[field4(all the child's id)] = '(' + f4 + ')'
FROM #x AS x1
WHERE NOT EXISTS
(
SELECT 1 FROM #x AS x2
WHERE x2.Field1 = x1.Field4
AND x2.f4 IS NOT NULL
);
DROP TABLE #x;
Results:
Field1 Field2 Field3 field4(all the child's id)
------ ------ ------ --------------------------
1 paper cash (2,3)
4 paper cash NULL
5 paper cash NULL
6 pen cash NULL
Cleanup:
DROP TABLE dbo.[Sample];
DROP FUNCTION dbo.ConcatIDs;
精彩评论