SQL Server Performance Forum – Threads Archive
Need help with this query
I have a Table like this Code1 code 2 code3 code4Code1 and code2 and code3 are not unique but code4 is unique set of Code1,Code2,code3 will have maximum of 3 Code4 values 1/2/3/4
1/2/3/5
1/2/3/6 as you can see that first 3 fields are not unique.
Now what i want to do is make this table
Code1 code 2 code3 code41 code42 code43 So that i get 4 5 6 in one row 1/2/3/4/5/6 Is it possible to make this table into a view and achieve this? Thanks for your help
Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true Madhivanan Failing to plan is Planning to fail
the idea in that article seems bit complicated
Could you suggest me a simpler method?
And in my table there are no fixed count as 3
ie each set of first 3 columns can have max of 3 different columns for
and minimum 1.
so if there are no codes for column2 and 3 exists then i should add 0 for that. Thanks for the quick reply
with regards
Cant you use Front End application to do this? In SQL, take this an example
Declare @str1 varchar(1000)
Declare @str2 varchar(1000) Declare @t table(comp varchar(20), name varchar(20), email varchar(50))
insert into @t values(‘Test’,’Test1′,’[email protected]‘)
insert into @t values(‘Test’,’Test2′,’[email protected]‘)
insert into @t values(‘Test’,’Test3′,’[email protected]‘) select * from @t Select @str1=Isnull(@str1+’,’,”)+name from @t
Select @str2=Isnull(@str2+’,’,”)+email from @t Select Distinct comp,@str1 as Names,@str2 as Emails from @t Madhivanan Failing to plan is Planning to fail
SELECT Code1 FROM MyTable
UNION
SELECT Code2 FROM MyTable
UNION
SELECT Code3 FROM MyTable
UNION
SELECT Code4 FROM MyTable … will give you all distinct values on those 4 columns – this would be the 1/2/3/4/5/6 list in your original posting. You could run a derived table on that list, and use correlated subqueries to look if the value is entered on each of the columns … SELECT T.LookupValue,
CASE WHEN EXISTS (SELECT Code1 FROM MyTable WHERE Code1 = T.LookupValue) THEN T.LookupValue ELSE 0 END,
CASE WHEN EXISTS (SELECT Code2 FROM MyTable WHERE Code2 = T.LookupValue) THEN T.LookupValue ELSE 0 END,
CASE WHEN EXISTS (SELECT Code3 FROM MyTable WHERE Code3 = T.LookupValue) THEN T.LookupValue ELSE 0 END,
CASE WHEN EXISTS (SELECT Code4 FROM MyTable WHERE Code4 = T.LookupValue) THEN T.LookupValue ELSE 0 END
FROM
(SELECT Code1 LookupValue FROM MyTable
UNION SELECT Code2 FROM MyTable
UNION SELECT Code3 FROM MyTable
UNION SELECT Code4 FROM MyTable) T
Hello Madhivanan
I did Tried with a simple ASP Script to do this part but it quits with connection timeout error. Thanks Adriaan for the help
let me try that
]]>