Transposing, filtering, most recent record

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Transposing, filtering, most recent record

Transposing, filtering, most recent record

Rate Topic

Display Mode

Topic Options

Author

Message

midavalo

Posted Sunday, October 07, 2012 1:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 4:36 PM
Points: 6, Visits: 21

Hi all,

A user at a PC help forum told me to try here.

I have a SQL database with a lot of tables with data being regularly modified. Some of the data needs to be transferred to another database on another server when it is updated.

I have an audit table that records every record when it is edited, however it records them in a field per row format, so for every field in an edited record there's a new row in the audit table to record the value of the field from that edited record. It records every field whether it has been edited or not.

I would like to have a view (or something) that transposes those fields into a single row for each edited record, filters to only have the data I need (so ignoring edited records in tables I don't care about), and the only display the most recent edit on any given record (so if its edited twice, I only need the most recent to update my second database).

Any ideas how to do any/all of the above?

Example of Audit Table:

AUDITID	RECORDID	EDITTABLE	EDITDATE		EDITUSER	EDITFIELD	EDITVALUE
1	1		Computers	2012-09-29 10:10:14.547	Mike		Brand		Acme
2	1		Computers	2012-09-29 10:10:14.547	Mike		RAM		2
3	1		Computers	2012-09-29 10:10:14.547	Mike		Colour		Red
4	2		Chairs      	2012-09-29 10:23:52.132	Bob		Brand		Comfy
5	2		Chairs      	2012-09-29 10:23:52.132	Bob		Colour		Blue
6	2		Chairs      	2012-09-29 10:23:52.132	Bob		Wheels		5
7	2		Chairs      	2012-09-29 10:23:52.132	Bob		Arms		No
8	3		Bottles   	2012-09-29 11:19:23.424	Mike		Capacity	300
9	3		Bottles   	2012-09-29 11:19:23.424	Mike		Lid		Yes
10	1		Computers	2012-09-30 14:32:26.223	Mike		Brand		Something
11	1		Computers	2012-09-30 14:32:26.223	Mike		RAM		4
12	1		Computers	2012-09-30 14:32:26.223	Mike		Colour		Red
13	4		Chairs      	2012-10-01 08:47:39.353	Sarah		Brand		NotSoComfy
14	4		Chairs      	2012-10-01 08:47:39.353	Sarah		Colour		Blue
15	4		Chairs      	2012-10-01 08:47:39.353	Sarah		Wheels		3
16	4		Chairs      	2012-10-01 08:47:39.353	Sarah		Arms		Yes

So above record 1 is edited twice, so I'd only need the most recent edits for it. The values in EDITFIELD would need to be columns, and values in EDITVALUE needs to be the values in those columns.

Any ideas?

Microsoft SQL Server 2008 R2 for database one, MS SQL Server 2008 for database two.

Cheers,
Mike.

Post #1369569

dwain.c

Posted Sunday, October 07, 2012 8:13 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 2,340, Visits: 3,167

First of all, it would be most useful if you put your sample data into consumable form and provide DDL, e.g.:

DECLARE @t TABLE (col1 VARCHAR(10), col2 VARCHAR(10))

INSERT INTO @t
SELECT 'abc', 'def'
UNION ALL SELECT 'ghi', 'klm'

Finally, you should show us what your expected output is supposed to look like.

Specifically, my question here is when you display the filtered columns across, does the column name shown need to be the column name as it's described in your sample data? That's going to be an issue because you have different tables (with different column names) in your sample data. So does it mean you're OK to produce one query for each table in your data?

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1369594

midavalo

Posted Sunday, October 07, 2012 8:30 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 4:36 PM
Points: 6, Visits: 21

dwain.c (10/7/2012)

First of all, it would be most useful if you put your sample data into consumable form and provide DDL, e.g.:

Excuse my ignorance, but what is DDL?

dwain.c (10/7/2012)

Specifically, my question here is when you display the filtered columns across, does the column name shown need to be the column name as it's described in your sample data? That's going to be an issue because you have different tables (with different column names) in your sample data. So does it mean you're OK to produce one query for each table in your data?

I would prefer the column names to match what is in the table. Whether that means multiple queries, or multiple extra columns in the result - I'm not too bothered either way.

Cheers,
Mike.

Post #1369597

midavalo

Posted Sunday, October 07, 2012 8:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 4:36 PM
Points: 6, Visits: 21

dwain.c (10/7/2012)

Finally, you should show us what your expected output is supposed to look like.

I'll try to post an expected output later today.

Mike.

Post #1369598

dwain.c

Posted Sunday, October 07, 2012 8:45 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 2,340, Visits: 3,167

midavalo (10/7/2012)

dwain.c (10/7/2012)

First of all, it would be most useful if you put your sample data into consumable form and provide DDL, e.g.:

Excuse my ignorance, but what is DDL?

Cheers,
Mike.

DDL=DATA DEFINITION LANGUAGE

Like:

CREATE TABLE #xxx (columns)

-- or as I did:

DECLARE @T TABLE (columns)

Post #1369602

midavalo

Posted Monday, October 08, 2012 1:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 4:36 PM
Points: 6, Visits: 21

Here is an example of possible output. Note the Values listed under 'EditFields' (in my example input table) are used as field names in the output. I don't necessarily need it this way - I could have multiple queries based on 'EditTable' or unrelated field names etc.

RecordID	EditTable	EditDate		EditUser	Brand		RAM	Colour	Wheels	Arms	Capacity	Lid
2		Chairs		2012-09-29 10:23:52.000	Bob		Comfy		NULL	NULL	5	No	NULL		NULL
3		Bottles		2012-09-29 11:19:23.000	Mike		NULL		NULL	NULL	NULL	NULL	300		Yes
1		Computers	2012-09-30 14:32:26.000	Mike		Something	4	Red	NULL	NULL	NULL		NULL
4		Chairs		2012-10-01 08:47:39.000	Sarah		NotSoComfy	NULL	Blue	3	Yes	NULL		NULL

Is this what you're after regarding 'consumable form and provide DDL'?

Example Audit Table:

CREATE TABLE [dbo].[m_Audit_Example](
	[AuditID] [int] NULL,
	[RecordID] [int] NULL,
	[EditTable] [varchar](20) NULL,
	[EditDate] [datetime] NULL,
	[EditUser] [varchar](20) NULL,
	[EditField] [varchar](20) NULL,
	[EditValue] [varchar](20) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (1, 1, N'Computers', CAST(0x0000A0DB00A79C34 AS DateTime), N'Mike', N'Brand', N'Acme')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (2, 1, N'Computers', CAST(0x0000A0DB00A79C34 AS DateTime), N'Mike', N'RAM', N'2')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (3, 1, N'Computers', CAST(0x0000A0DB00A79C34 AS DateTime), N'Mike', N'Colour', N'Red')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (4, 2, N'Chairs', CAST(0x0000A0DB00AB59A0 AS DateTime), N'Bob', N'Brand', N'Comfy')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (5, 2, N'Chairs', CAST(0x0000A0DB00AB59A0 AS DateTime), N'Bob', N'Colour', N'Blue')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (6, 2, N'Chairs', CAST(0x0000A0DB00AB59A0 AS DateTime), N'Bob', N'Wheels', N'5')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (7, 2, N'Chairs', CAST(0x0000A0DB00AB59A0 AS DateTime), N'Bob', N'Arms', N'No')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (8, 3, N'Bottles', CAST(0x0000A0DB00BA9924 AS DateTime), N'Mike', N'Capacity', N'300')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (9, 3, N'Bottles', CAST(0x0000A0DB00BA9924 AS DateTime), N'Mike', N'Lid', N'Yes')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (10, 1, N'Computers', CAST(0x0000A0DC00EF9EF8 AS DateTime), N'Mike', N'Brand', N'Something')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (11, 1, N'Computers', CAST(0x0000A0DC00EF9EF8 AS DateTime), N'Mike', N'RAM', N'4')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (12, 1, N'Computers', CAST(0x0000A0DC00EF9EF8 AS DateTime), N'Mike', N'Colour', N'Red')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (13, 4, N'Chairs', CAST(0x0000A0DD0090EC64 AS DateTime), N'Sarah', N'Brand', N'NotSoComfy')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (14, 4, N'Chairs', CAST(0x0000A0DD0090EC64 AS DateTime), N'Sarah', N'Colour', N'Blue')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (15, 4, N'Chairs', CAST(0x0000A0DD0090EC64 AS DateTime), N'Sarah', N'Wheels', N'3')
INSERT [dbo].[m_Audit_Example] ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (16, 4, N'Chairs', CAST(0x0000A0DD0090EC64 AS DateTime), N'Sarah', N'Arms', N'Yes')

Example Audit Result Table:

CREATE TABLE [dbo].[m_Audit_Result_Example](
	[RecordID] [int] NULL,
	[EditTable] [varchar](20) NULL,
	[EditDate] [datetime] NULL,
	[EditUser] [varchar](20) NULL,
	[Brand] [varchar](20) NULL,
	[RAM] [varchar](20) NULL,
	[Colour] [varchar](20) NULL,
	[Wheels] [varchar](20) NULL,
	[Arms] [varchar](20) NULL,
	[Capacity] [varchar](20) NULL,
	[Lid] [varchar](20) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[m_Audit_Result_Example] ([RecordID], [EditTable], [EditDate], [EditUser], [Brand], [RAM], [Colour], [Wheels], [Arms], [Capacity], [Lid]) VALUES (2, N'Chairs', CAST(0x0000A0DB00AB59A0 AS DateTime), N'Bob', N'Comfy', NULL, NULL, N'5', N'No', NULL, NULL)
INSERT [dbo].[m_Audit_Result_Example] ([RecordID], [EditTable], [EditDate], [EditUser], [Brand], [RAM], [Colour], [Wheels], [Arms], [Capacity], [Lid]) VALUES (3, N'Bottles', CAST(0x0000A0DB00BA9924 AS DateTime), N'Mike', NULL, NULL, NULL, NULL, NULL, N'300', N'Yes')
INSERT [dbo].[m_Audit_Result_Example] ([RecordID], [EditTable], [EditDate], [EditUser], [Brand], [RAM], [Colour], [Wheels], [Arms], [Capacity], [Lid]) VALUES (1, N'Computers', CAST(0x0000A0DC00EF9EF8 AS DateTime), N'Mike', N'Something', N'4', N'Red', NULL, NULL, NULL, NULL)
INSERT [dbo].[m_Audit_Result_Example] ([RecordID], [EditTable], [EditDate], [EditUser], [Brand], [RAM], [Colour], [Wheels], [Arms], [Capacity], [Lid]) VALUES (4, N'Chairs', CAST(0x0000A0DD0090EC64 AS DateTime), N'Sarah', N'NotSoComfy', NULL, N'Blue', N'3', N'Yes', NULL, NULL)

What I'd like to know is whether it is possible to have a query or view that will take my input data and output it in format similar to my result example? I don't mind if fieldnames can't be the same or if there are multiple queries involved.

Thanks,
Mike.

Post #1370010

dwain.c

Posted Monday, October 08, 2012 7:01 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 2,340, Visits: 3,167

Your DDL is nearly perfect except that I prefer seeing a temp table or table variable so that I don't create actual tables in my sandbox. Setup data is fine. So, with a temp table:

CREATE TABLE #m_Audit_Example(
	[AuditID] [int] NULL,
	[RecordID] [int] NULL,
	[EditTable] [varchar](20) NULL,
	[EditDate] [datetime] NULL,
	[EditUser] [varchar](20) NULL,
	[EditField] [varchar](20) NULL,
	[EditValue] [varchar](20) NULL
) ON [PRIMARY]
GO
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (1, 1, N'Computers', CAST(0x0000A0DB00A79C34 AS DateTime), N'Mike', N'Brand', N'Acme')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (2, 1, N'Computers', CAST(0x0000A0DB00A79C34 AS DateTime), N'Mike', N'RAM', N'2')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (3, 1, N'Computers', CAST(0x0000A0DB00A79C34 AS DateTime), N'Mike', N'Colour', N'Red')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (4, 2, N'Chairs', CAST(0x0000A0DB00AB59A0 AS DateTime), N'Bob', N'Brand', N'Comfy')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (5, 2, N'Chairs', CAST(0x0000A0DB00AB59A0 AS DateTime), N'Bob', N'Colour', N'Blue')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (6, 2, N'Chairs', CAST(0x0000A0DB00AB59A0 AS DateTime), N'Bob', N'Wheels', N'5')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (7, 2, N'Chairs', CAST(0x0000A0DB00AB59A0 AS DateTime), N'Bob', N'Arms', N'No')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (8, 3, N'Bottles', CAST(0x0000A0DB00BA9924 AS DateTime), N'Mike', N'Capacity', N'300')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (9, 3, N'Bottles', CAST(0x0000A0DB00BA9924 AS DateTime), N'Mike', N'Lid', N'Yes')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (10, 1, N'Computers', CAST(0x0000A0DC00EF9EF8 AS DateTime), N'Mike', N'Brand', N'Something')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (11, 1, N'Computers', CAST(0x0000A0DC00EF9EF8 AS DateTime), N'Mike', N'RAM', N'4')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (12, 1, N'Computers', CAST(0x0000A0DC00EF9EF8 AS DateTime), N'Mike', N'Colour', N'Red')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (13, 4, N'Chairs', CAST(0x0000A0DD0090EC64 AS DateTime), N'Sarah', N'Brand', N'NotSoComfy')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (14, 4, N'Chairs', CAST(0x0000A0DD0090EC64 AS DateTime), N'Sarah', N'Colour', N'Blue')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (15, 4, N'Chairs', CAST(0x0000A0DD0090EC64 AS DateTime), N'Sarah', N'Wheels', N'3')
INSERT #m_Audit_Example ([AuditID], [RecordID], [EditTable], [EditDate], [EditUser], [EditField], [EditValue]) VALUES (16, 4, N'Chairs', CAST(0x0000A0DD0090EC64 AS DateTime), N'Sarah', N'Arms', N'Yes')

SELECT * FROM #m_Audit_Example

The trick seemed to be in identifying the second (or last) of a group of audits, which I accomplished with the 2 ROW_NUMBER() functions in the MyAudits CTE. The remainder used two cross tabs (or you could use PIVOT as well), but I prefer cross tabs.

;WITH MyAudits AS (
        SELECT RecordID, EditTable, EditDate, EditUser, EditField, EditValue
            ,rn=ROW_NUMBER() OVER (PARTITION BY EditUser, EditTable, rn ORDER BY EditDate DESC)
        FROM (
            SELECT RecordID, EditTable, EditDate, EditUser, EditField, EditValue
                ,rn=ROW_NUMBER() OVER (PARTITION BY EditUser, EditTable, EditDate ORDER BY RecordID DESC)
            FROM #m_Audit_Example) a ),
    MyGroups AS (
        SELECT RecordID, EditTable, EditDate, EditUser
            ,Brand  = CASE WHEN EditField = 'Brand'     THEN MAX(EditValue) ELSE NULL END
            ,RAM    = CASE WHEN EditField = 'RAM'       THEN MAX(EditValue) ELSE NULL END
            ,Colour = CASE WHEN EditField = 'Colour'    THEN MAX(EditValue) ELSE NULL END
            ,Wheels = CASE WHEN EditField = 'Wheels'    THEN MAX(EditValue) ELSE NULL END
            ,Arms   = CASE WHEN EditField = 'Arms'      THEN MAX(EditValue) ELSE NULL END
            ,Capacity=CASE WHEN EditField = 'Capacity'  THEN MAX(EditValue) ELSE NULL END
            ,Lid    = CASE WHEN EditField = 'Lid'       THEN MAX(EditValue) ELSE NULL END
        FROM MyAudits
        WHERE rn=1
        GROUP BY RecordID, EditTable, EditDate, EditUser, EditField)
SELECT RecordID, EditTable, EditDate, EditUser
            ,Brand=MAX(Brand)
            ,RAM=MAX(RAM)
            ,Colour=MAX(Colour)
            ,Wheels=MAX(Wheels)
            ,Arms=MAX(Arms)
            ,Capacity=MAX(Capacity)
            ,Lid=MAX(Lid)
FROM MyGroups
GROUP BY RecordID, EditTable, EditDate, EditUser

DROP TABLE #m_Audit_Example

Now you didn't say (and I forgot to ask) if the number of edit fields is fixed or could vary beyond the columns I've created. If it is variable, you'd need to use Dynamic SQL. Build the above in a string using some method to elucidate your edit fields and then execute it.

Note that your expected results looked good too, except there's a minor discrepancy in the results for Bob.

Post #1370123

midavalo

Posted Monday, October 08, 2012 8:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 4:36 PM
Points: 6, Visits: 21

dwain.c (10/8/2012)

Thanks for your response. This query gives the expected result (I must have missed a value when entering in the sample result for 'Bob'). Much appreciated.

The number of tables listed in the audit that I need to look at is around 35 (the audit will contain many more than this), and the number of fields will vary between the tables from ~15 to ~30. Many of the fieldnames will overlap the different tables. There are a total of 51 different fields. These 51 are fairly fixed (unlikely to have any new ones added). Your mention of dynamic SQL just went over my head :)

Many thanks for your help.

Mike.

Post #1370134

dwain.c

Posted Tuesday, October 09, 2012 4:39 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 2,340, Visits: 3,167

I always find it best to learn from examples, so here for you is an example of how you'd set this up to execute as dynamic SQL:

DECLARE @SQL    VARCHAR(MAX)
    ,@SQL1      VARCHAR(MAX) = ''
    ,@SQL2      VARCHAR(MAX) = ''

;WITH EditFields AS (
    SELECT DISTINCT EditField
    FROM #m_Audit_Example)
SELECT @SQL1 = @SQL1 + ',' + EditField + ' = CASE WHEN EditField = ''' + EditField + ''' THEN MAX(EditValue) ELSE NULL END'
    ,@SQL2 = @SQL2 + ',' + EditField + '=MAX(' + EditField + ')'
FROM EditFields

SELECT @SQL = '
;WITH MyAudits AS (
        SELECT RecordID, EditTable, EditDate, EditUser, EditField, EditValue
            ,rn=ROW_NUMBER() OVER (PARTITION BY EditUser, EditTable, rn ORDER BY EditDate DESC)
        FROM (
            SELECT RecordID, EditTable, EditDate, EditUser, EditField, EditValue
                ,rn=ROW_NUMBER() OVER (PARTITION BY EditUser, EditTable, EditDate ORDER BY RecordID DESC)
            FROM #m_Audit_Example) a ),
    MyGroups AS (
        SELECT RecordID, EditTable, EditDate, EditUser ' + @SQL1 + 
            --,Brand  = CASE WHEN EditField = 'Brand'     THEN MAX(EditValue) ELSE NULL END
            --,RAM    = CASE WHEN EditField = 'RAM'       THEN MAX(EditValue) ELSE NULL END
            --,Colour = CASE WHEN EditField = 'Colour'    THEN MAX(EditValue) ELSE NULL END
            --,Wheels = CASE WHEN EditField = 'Wheels'    THEN MAX(EditValue) ELSE NULL END
            --,Arms   = CASE WHEN EditField = 'Arms'      THEN MAX(EditValue) ELSE NULL END
            --,Capacity=CASE WHEN EditField = 'Capacity'  THEN MAX(EditValue) ELSE NULL END
            --,Lid    = CASE WHEN EditField = 'Lid'       THEN MAX(EditValue) ELSE NULL END
        ' FROM MyAudits
        WHERE rn=1
        GROUP BY RecordID, EditTable, EditDate, EditUser, EditField)
SELECT RecordID, EditTable, EditDate, EditUser ' + @SQL2 +
            --,Brand=MAX(Brand)
            --,RAM=MAX(RAM)
            --,Colour=MAX(Colour)
            --,Wheels=MAX(Wheels)
            --,Arms=MAX(Arms)
            --,Capacity=MAX(Capacity)
            --,Lid=MAX(Lid)
' FROM MyGroups
GROUP BY RecordID, EditTable, EditDate, EditUser'

EXEC (@SQL)

Notes:
1) You may want to look into sp_executesql as an alternative to EXEC(@SQL) as it allows for better control.
2) The columns (EditValue) are listed across in alphabetic order. As many as you need. The ordering is established where I do SELECT DISTINCT to create @SQL1 and @SQL2 (the two variable parts of the SQL).
3) The static part of @SQL can be obtained directly from the static version of the query. You can see I've done this by simply enclosing selected parts in quotation marks and then adding in concatenations with @SQL1 and @SQL2 as needed. I left the parts replaced by @SQL1 and @SQL2 in but commented out.

Post #1370297

midavalo

Posted Thursday, October 11, 2012 4:38 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2012 4:36 PM
Points: 6, Visits: 21

thanks so much for your help dwain.c

Mike.

Post #1371823

« Prev Topic | Next Topic »

Permissions