Transposing, filtering, most recent record

  • 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.

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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.

  • 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.

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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.

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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.

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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