June 12, 2009 at 3:56 am
Hi Folks,
I'm trying to get details from different environment variables without having duplicate entries in the results.
I'd like to display the three variables, and their values, on three different cols, and not have any duplicates. At the moment the three variable can all be the same on different machines and this is causing problems in my report results.
Here's an example of the code.
DECLARE @VAR1 CHAR(20), @VAR2 CHAR(20), @VAR3 CHAR(20)
SET @VAR1='Variable1'
SET @VAR2='Variable2'
SET @VAR3='Variable3'
Select Distinct
SYS.User_Name0,
SYS.User_Domain0,
ENV.Name0,
ENV.VariableValue0
FROM v_GS_ENVIRONMENT ENV
INNER JOIN v_R_System SYS
ON SYS.resourceID = Env.ResourceID
WHERE ENV.Name0 =@VAR1
OR ENV.Name0 = @VAR2
OR ENV.Name0 = @VAR3
AND SYS.User_Domain0 LIKE 'DOMAIN'
Order By
SYS.User_Domain0 DESC
June 12, 2009 at 6:53 am
Hi J,
It's far from clear to me what you mean: when you talk about 'duplicates', do you talk about records in the 2 tables? Or do you mean the values in the respective columns? Or do you mean value in ANY coloumn?
Maybe you could provide us with 2 testtables with some data and a description what you would expect from this data?
Greetz,
Hans Brouwer
June 12, 2009 at 7:44 am
Hi Hans,
Thanks for the reply!
This is the data I get back:
User Name User Domain Name Variable Value
userID1 DOMAIN VAR1 VALUE1
userID2 DOMAIN VAR1 VALUE1
userID3 DOMAIN VAR1 VALUE1
userID4 DOMAIN VAR1 VALUE1
userID4 DOMAIN VAR3 VALUE3
userID5 DOMAIN VAR1 VALUE1
userID5 DOMAIN VAR2 VALUE2
As you can see the duplicates are as a result of the query asking for var1, var2 and var3 values, without being able to group them together into a table which might look like:
User Name User Domain Name1 Name2 Name3 Variable Value1 Variable Value2 etc.
userID1 DOMAIN VAR1 VAR2 VAR3 VALUE1 VALUE2
June 12, 2009 at 7:45 am
Hi Hans,
Thanks for the reply!
This is the data I get back:
User Name User Domain Name Variable Value
userID1 DOMAIN VAR1 VALUE1
userID2 DOMAIN VAR1 VALUE1
userID3 DOMAIN VAR1 VALUE1
userID4 DOMAIN VAR1 VALUE1
userID4 DOMAIN VAR3 VALUE3
userID5 DOMAIN VAR1 VALUE1
userID5 DOMAIN VAR2 VALUE2
As you can see the duplicates are as a result of the query asking for var1, var2 and var3 values, without being able to group them together into a table which might look like:
User Name User Domain Name1 Name2 Name3 Variable Value1 Variable Value2 etc.
userID1 DOMAIN VAR1 VAR2 VAR3 VALUE1 VALUE2
Sorry it's all a bit vague. If I can make things clearer please let me know.
Cheers
J
June 12, 2009 at 8:15 am
Try it like this:
DECLARE @VAR1 CHAR(20), @VAR2 CHAR(20), @VAR3 CHAR(20)
SET @VAR1='Variable1'
SET @VAR2='Variable2'
SET @VAR3='Variable3'
Select Distinct
SYS.User_Name0,
SYS.User_Domain0,
@VAR1 As [Var1],
@VAR1 As [Var2],
@VAR1 As [Var3],
MAX( CASE WHEN ENV.Name0 = @VAR1
THEN ENV.VariableValue0
END ) As [Value1],
MAX( CASE WHEN ENV.Name0 = @VAR2
THEN ENV.VariableValue0
END ) As [Value2],
MAX( CASE WHEN ENV.Name0 = @VAR3
THEN ENV.VariableValue0
END ) As [Value3]
FROM v_GS_ENVIRONMENT ENV
INNER JOIN v_R_System SYS
ON SYS.resourceID = Env.ResourceID
WHERE ENV.Name0 =@VAR1
OR ENV.Name0 = @VAR2
OR ENV.Name0 = @VAR3
AND SYS.User_Domain0 LIKE 'DOMAIN'
Group By SYS.User_Name0, SYS.User_Domain0
Order By
SYS.User_Domain0 DESC
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 12, 2009 at 9:04 am
You probly want a PIVOT table; lookup PIVOT in BOL.
If it is for just 2 of the fields I can do it like this:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_3](
[Username] [nchar](30) NULL,
[UserDomain] [nchar](30) NULL,
[Var] [nchar](10) NULL,
[VarValue] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT Table_3 VAL(User1,Domain1,Var1,Val1)
INSERT Table_3 VAL(User2,Domain1,Var1,Val10
INSERT Table_3 VAL(User2,Domain1,Var2,Val2)
INSERT Table_3 VAL(User2,Domain1,Var3,Val3)
INSERT Table_3 VAL(User3,Domain1,Var2,Val2)
GO
SELECT Username, UserDomain,[Var1] AS Var1, [Var2] AS Var2, [Var3] AS Var3
FROM
(SELECT Username,UserDomain, [Var]
FROM Table_3) p
PIVOT
(
COUNT ([Var])
FOR [Var] IN
( [Var1], [Var2], [Var3] )
) AS pvt
ORDER BY Username
If you want the VarValue also grouped like that, and possibly UserDomain as well, this does not work, Pivot is 2-dimensional. You would have to create 1 or more temp tables and manually group your result sets. I would not like to do this.
You could Google for recursion in sql server.
If you don't mind concatenation of some of your info, have a look at this method: http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html
Hopefully this will help you to a solution.
Greetz,
Hans Brouwer
June 12, 2009 at 9:35 am
HI Folks,
Thanks for the replies!
Hans I don't have permission to create or modify tables so my hands are tied in that respect but I'll have a look at the link and keep your code as it will come in useful later I think.
Barry I've modified what you've done a bit and it works exactly as I want it now (pretty much 🙂 ). It worked perfect6ly as it was but I needed to change up a couple of bits when I saw the output.
Anyhow thanks for that. I'll have a footer with this and see what I can learn from it then post my next question related to this code 🙂
Cheers
J
June 12, 2009 at 9:43 am
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply