June 8, 2011 at 3:33 pm
I have a table with the following data:
A_Pfxa_policyA_DATE_ADDEDA_PROCESSDATEA_NUM_PROCESSEDEndRReq CertPremaud AudDisp
GL69269509/15/200908/16/20090 X
GL69269509/15/200908/16/20090 X
GL69269509/15/200908/16/20090
GL69269509/15/200908/16/20090 X
GL69269509/15/200908/16/20090
GL69269509/15/200908/16/20090 X
Trying to come up with SQL script for this result - one row with all the data for the above rows:
A_Pfxa_policyA_DATE_ADDEDA_PROCESSDATEA_NUM_PROCESSEDEndRReq CertPremaud AudDisp
GL69269509/15/200908/16/20090 X X X X
I've tried cross applys, coalesce, cte's, table variables....but can't seem to get the result.
Sorry for the compressed format - see the attached doc, combined rpws.docx
Any ideas out there greatly appreciated!
June 8, 2011 at 3:42 pm
I had a similar Issue and I prepared the below query , hope fully it helps you too.
CREATE FUNCTION dbo.udf_select_concat ( @C varchar(255))
RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @p VARCHAR(MAX) ;
SET @p = '' ;
SELECT @p = @p + specs + ','
FROM databasename..tablename WHERE column_name = @C ;
RETURN @p
END
Query
SELECT
column_name,
dbo.udf_select_concat( column_name) as column_name
FROM databasename..tablename
GROUP BY columnname
June 8, 2011 at 3:43 pm
June 8, 2011 at 4:20 pm
James A. Lawrence (6/8/2011)
I have a table with the following data:A_Pfxa_policyA_DATE_ADDEDA_PROCESSDATEA_NUM_PROCESSEDEndRReq CertPremaud AudDisp
GL69269509/15/200908/16/20090 X
GL69269509/15/200908/16/20090 X
GL69269509/15/200908/16/20090
GL69269509/15/200908/16/20090 X
GL69269509/15/200908/16/20090
GL69269509/15/200908/16/20090 X
Trying to come up with SQL script for this result - one row with all the data for the above rows:
A_Pfxa_policyA_DATE_ADDEDA_PROCESSDATEA_NUM_PROCESSEDEndRReq CertPremaud AudDisp
GL69269509/15/200908/16/20090 X X X X
I've tried cross applys, coalesce, cte's, table variables....but can't seem to get the result.
Sorry for the compressed format - see the attached doc, combined rpws.docx
Any ideas out there greatly appreciated!
Nice job providing your expected results. Please provide usable DDL and DML to create some test data next time.
See if this solution works for you:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.t1')
AND type IN (N'U') )
DROP TABLE dbo.t1
GO
CREATE TABLE dbo.t1
(
A_Pfx CHAR(2),
a_policy INT,
A_DATE_ADDED DATE,
A_PROCESSDATE DATE,
A_NUM_PROCESSED INT,
EndRReq CHAR(1),
[Cert] CHAR(1),
Premaud CHAR(1),
AudDisp CHAR(1)
) ;
INSERT INTO dbo.t1
(A_Pfx, a_policy, A_DATE_ADDED, A_PROCESSDATE, A_NUM_PROCESSED, EndRReq, [Cert], Premaud, AudDisp)
VALUES ('GL', 692695, '09/15/2009', '08/16/2009', 0, '', '', '', 'X'),
('GL', 692695, '09/15/2009', '08/16/2009', 0, 'X', '', '', ''),
('GL', 692695, '09/15/2009', '08/16/2009', 0, '', '', '', ''),
('GL', 692695, '09/15/2009', '08/16/2009', 0, '', '', 'X', ''),
('GL', 692695, '09/15/2009', '08/16/2009', 0, '', '', '', ''),
('GL', 692695, '09/15/2009', '08/16/2009', 0, '', 'X', '', '') ;
SELECT *
FROM dbo.t1
SELECT A_Pfx,
a_policy,
A_DATE_ADDED,
A_PROCESSDATE,
A_NUM_PROCESSED,
MAX(EndRReq) AS EndRReq,
MAX([Cert]) AS [Cert],
MAX(Premaud) AS Premaud,
MAX(AudDisp) AS AudDisp
FROM dbo.t1
GROUP BY A_Pfx,
a_policy,
A_DATE_ADDED,
A_PROCESSDATE,
A_NUM_PROCESSED ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 9, 2011 at 10:18 am
Thanks to all for your input on this!
I actually was able to find yet one more way to get this to work!
select distinct
t1.A_PREFIX,
t1.a_policy,
a_date_added, a_processdate, a_num_processed,
(SELECT endrreq from vw_audit_policy_images where a_prefix = T1.a_prefix and a_policy = T1.a_policy and endrreq = 'X') as endrreq,
(SELECT cert from vw_audit_policy_images where a_prefix = T1.a_prefix and a_policy = T1.a_policy and cert = 'X') as cert,
(SELECT premaud from vw_audit_policy_images where a_prefix = T1.a_prefix and a_policy = T1.a_policy and premaud = 'X') as premaud,
(SELECT auddisp from vw_audit_policy_images where a_prefix = T1.a_prefix and a_policy = T1.a_policy and auddisp = 'X') as auddisp
FROM mySQLView t1
June 9, 2011 at 10:30 am
James A. Lawrence (6/9/2011)
Thanks to all for your input on this!I actually was able to find yet one more way to get this to work!
select distinct
t1.A_PREFIX,
t1.a_policy,
a_date_added, a_processdate, a_num_processed,
(SELECT endrreq from vw_audit_policy_images where a_prefix = T1.a_prefix and a_policy = T1.a_policy and endrreq = 'X') as endrreq,
(SELECT cert from vw_audit_policy_images where a_prefix = T1.a_prefix and a_policy = T1.a_policy and cert = 'X') as cert,
(SELECT premaud from vw_audit_policy_images where a_prefix = T1.a_prefix and a_policy = T1.a_policy and premaud = 'X') as premaud,
(SELECT auddisp from vw_audit_policy_images where a_prefix = T1.a_prefix and a_policy = T1.a_policy and auddisp = 'X') as auddisp
FROM mySQLView t1
Nice work! Be careful though...your solution has hidden RBAR processing built into it. If you run your solution on a large data set your database server will not appreciate it. When I ran the query the execution shows tons of nested loops, whereas mine is a simple aggregate meaning one pass over the data and no internal looping in the execution plan.
If you're not sure what I'm talking about feel free to ask more questions. Here are two good articles on the topic of hidden RBAR:
Originator Jeff Moden: http://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/[/url]
Another good overview: http://qa.sqlservercentral.com/articles/T-SQL/61539/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply