January 28, 2014 at 2:26 pm
I have a problem that I am trying to address and cannot think of the best way to go about it. I am constrained in that the application cannot change so I need to perform this work all within the existing query.
This query is used to return feedback from a employee as well as their peers. There are 3 requirements: 1) that the names always are returned in a different order to the application, 2) that the comments provided by the given employee (recordtype = 1) remain tied to their name, and 3) that there is no way to tie comments to a given peer (recordtype = 2). In the current query, we are achieving #1 and #2 by using the ORDER BY NEWID(). What I am not achieving is moving the comments around for the recordtype 2 records so they are not tied to a given name.
By the way, the existing application (which cannot change), is loading the data in the same order returned by this query. So, as long as you can see the order the names show up in, you know who provided what feedback.
What is the best way to go about still returning the same number of rows, making sure recordtype 1 record is intact, but returning the comments (both 1 and 2) in different orders? Below is sample data for populating a table with test data for use.
I appreciate all of your help in figuring this out!
DECLARE @CommentData TABLE (RecordType INT, fullname VARCHAR(50), comment1 VARCHAR(250), comment2 VARCHAR(250))
INSERT @CommentData
([RecordType]
,[fullname]
,[comment1]
,[comment2])
SELECT 1, 'SelfName', 'This is my comment1', 'This is my comment2'
UNION
SELECT 2, 'Name1', 'Name1 Comment1', 'Name1 Comment2'
UNION
SELECT 2, 'Name2', 'Name2 Comment1', 'Name2 Comment2'
UNION
SELECT 2, 'Name3', 'Name3 Comment1', 'Name3 Comment2'
UNION
SELECT 2, 'Name4', 'Name4 Comment1', 'Name4 Comment2'
UNION
SELECT 2, 'Name5', 'Name5 Comment1', 'Name5 Comment2'
UNION
SELECT 2, 'Name6', 'Name6 Comment1', 'Name6 Comment2'
SELECT
[RecordType]
,[fullname]
,[comment1]
,[comment2]
FROM @CommentData
ORDER BY NEWID()
January 28, 2014 at 2:45 pm
Awesome job posting ddl and sample data. Your explanation was quite clear too. This seems to be an art form that so many people are unwilling or unable to do lately. Thank you!!!
Here is one way you can do something like this. In essence we get a Row_Number for each column that is randomized, then join back to the original table. This should sufficiently "mix" the rows, especially if you have a lot of rows.
with RecordType as
(
SELECT [RecordType], ROW_NUMBER() over (order by newid()) as RowNum
FROM @CommentData
),
FullName as
(
SELECT [fullname], ROW_NUMBER() over (order by newid()) as RowNum
FROM @CommentData
),
Comment1 as
(
SELECT [comment1], ROW_NUMBER() over (order by newid()) as RowNum
FROM @CommentData
),
Comment2 as
(
SELECT [comment2], ROW_NUMBER() over (order by newid()) as RowNum
FROM @CommentData
)
select rt.RecordType, fn.fullname, c1.comment1, c2.comment2
from RecordType rt
join FullName fn on fn.RowNum = rt.RowNum
join Comment1 c1 on c1.RowNum = rt.RowNum
join Comment2 c2 on c2.RowNum = rt.RowNum;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 29, 2014 at 9:50 am
Sean,
Thank you so much for your help. It definitely headed me in the right direction. I did need to tweak it as implementing that way would break requirement #2 as the employees comments were also being randomly assigned. I modifiied the CTE to only work with RecordType 2 records. At first, I simply included a UNION back to the base table to get recordtype 1. By doing this, it broke requirement #1 in that all the employees were being returned in the same order everytime. To get to the point of meeting all 3 requirements, I had to return the results of the CTE into another table including an additional NEWID column. I then UNION'd this to the base table to get recordtype 1 including a NEWID column and then ordering by this. This does appear to have met all 3 requirements now. Is there anything you would recommend with this approach?
Again, thank you for your feedback as it was a HUGE help!
Chad
CREATE TABLE #CommentData (RecordType INT, fullname VARCHAR(50), comment1 VARCHAR(250), comment2 VARCHAR(250))
INSERT INTO #CommentData
([RecordType]
,[fullname]
,[comment1]
,[comment2])
SELECT 1, 'SelfName', 'This is my comment1', 'This is my comment2'
UNION
SELECT 2, 'Name1', 'Name1 Comment1', 'Name1 Comment2'
UNION
SELECT 2, 'Name2', 'Name2 Comment1', 'Name2 Comment2'
UNION
SELECT 2, 'Name3', 'Name3 Comment1', 'Name3 Comment2'
UNION
SELECT 2, 'Name4', 'Name4 Comment1', 'Name4 Comment2'
UNION
SELECT 2, 'Name5', 'Name5 Comment1', 'Name5 Comment2'
UNION
SELECT 2, 'Name6', 'Name6 Comment1', 'Name6 Comment2'
GO
with RecordType as
(
SELECT [RecordType], ROW_NUMBER() over (order by newid()) as RowNum
FROM #CommentData
WHERE [RecordType] = 2
),
FullName as
(
SELECT [fullname], ROW_NUMBER() over (order by newid()) as RowNum
FROM #CommentData
WHERE [RecordType] = 2
),
Comment1 as
(
SELECT [comment1], ROW_NUMBER() over (order by newid()) as RowNum
FROM #CommentData
WHERE [RecordType] = 2
),
Comment2 as
(
SELECT [comment2], ROW_NUMBER() over (order by newid()) as RowNum
FROM #CommentData
WHERE [RecordType] = 2
)
select rt.RecordType, fn.fullname, c1.comment1, c2.comment2, NEWID() AS ord
INTO #t1
from RecordType rt
join FullName fn on fn.RowNum = rt.RowNum
join Comment1 c1 on c1.RowNum = rt.RowNum
join Comment2 c2 on c2.RowNum = rt.RowNum
;
SELECT [RecordType]
,[fullname]
,[comment1]
,[comment2]
,NEWID() AS ord
FROM [#CommentData]
WHERE [RecordType] = 1
UNION
SELECT [RecordType]
,[fullname]
,[comment1]
,[comment2]
,[ord]
FROM [#t1]
ORDER BY [ord]
DROP TABLE [#CommentData]
DROP TABLE #t1
January 29, 2014 at 10:03 am
Chad, glad that worked for you and thanks for posting your final query that worked.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply