October 7, 2012 at 1:39 pm
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:
AUDITIDRECORDIDEDITTABLEEDITDATEEDITUSEREDITFIELDEDITVALUE
11Computers2012-09-29 10:10:14.547MikeBrandAcme
21Computers2012-09-29 10:10:14.547MikeRAM2
31Computers2012-09-29 10:10:14.547MikeColourRed
42Chairs 2012-09-29 10:23:52.132BobBrandComfy
52Chairs 2012-09-29 10:23:52.132BobColourBlue
62Chairs 2012-09-29 10:23:52.132BobWheels5
72Chairs 2012-09-29 10:23:52.132BobArmsNo
83Bottles 2012-09-29 11:19:23.424MikeCapacity300
93Bottles 2012-09-29 11:19:23.424MikeLidYes
101Computers2012-09-30 14:32:26.223MikeBrandSomething
111Computers2012-09-30 14:32:26.223MikeRAM4
121Computers2012-09-30 14:32:26.223MikeColourRed
134Chairs 2012-10-01 08:47:39.353SarahBrandNotSoComfy
144Chairs 2012-10-01 08:47:39.353SarahColourBlue
154Chairs 2012-10-01 08:47:39.353SarahWheels3
164Chairs 2012-10-01 08:47:39.353SarahArmsYes
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.
October 7, 2012 at 8:13 pm
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?
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 7, 2012 at 8:30 pm
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.
October 7, 2012 at 8:33 pm
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.
October 7, 2012 at 8:45 pm
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)
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 8, 2012 at 1:32 pm
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.
RecordIDEditTableEditDateEditUserBrandRAMColourWheelsArmsCapacityLid
2Chairs2012-09-29 10:23:52.000BobComfyNULLNULL5NoNULLNULL
3Bottles2012-09-29 11:19:23.000MikeNULLNULLNULLNULLNULL300Yes
1Computers2012-09-30 14:32:26.000MikeSomething4RedNULLNULLNULLNULL
4Chairs2012-10-01 08:47:39.000SarahNotSoComfyNULLBlue3YesNULLNULL
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.
October 8, 2012 at 7:01 pm
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 8, 2012 at 8:34 pm
dwain.c (10/8/2012)
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.
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.
October 9, 2012 at 4:39 am
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 11, 2012 at 4:38 pm
thanks so much for your help dwain.c
Mike.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply