Query help - table with 150 columns

Question

I have one table with 5 columns

Comments, Table1, Table2, Table3, Table4
null,      A,     B,      C,      D
null,      F,     B,      A,      G
null,      A,     S,      B,      D
null,      E,     A,      J,      S

now i want to update like that

update t
  set Comments = Comments + case when t.Table1 = 'A' then 'Table1--'
                                 when t.Table2 = 'B' then 'Table2--'
from #temp
 
After update My Expected Result is

Comments,          Table1, Table2, Table3, Table4
Table1--Table2,      A,     B,      C,      D
Table2,              F,     B,      A,      G
Table1,              A,     S,      B,      D
null,                E,     A,      J,      S

Please not suggest to check both column in same case as here I mention very simple condition actualy I have table with 150 columns and with different check and I want to mention comments when cretira match.

Thanks

Answer 1

declare @t table (Comments varchar(100), Table1 char(1)
, Table2 char(1)
, Table3 char(1)
, Table4 char(1)
)
insert @t  
 values (null, 'A', 'B', 'C','D'),
 (null, 'F', 'B', 'A','G'),
 (null, 'A', 'S', 'B','D'),
 (null, 'E', 'A', 'J','S')
;
update @t 
  set Comments = Nullif( isnull(Comments , '') +
    stuff( 
     isnull ('--' + case when Table1 = 'A' then 'Table1' else null end, '')
     + isnull ('--' + case when Table2 = 'B' then 'Table2' else null end, '')
     + isnull ('--' + case when Table3 = 'C' then 'Table3' else null end, '')
     + isnull ('--' + case when Table4 = 'D' then 'Table4' else null end, '')
	 , 1,  2 , '')
  , '');

  select * from @t

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

Hi -

I am not clear on what you are asking.  And, according to your example and update condition, the results for the first row don't make sense.

Also, since you have NULL in the Comments column, maybe you should check for NULL and use an empty string ('') if it is null.  I use the Coalesce function below.  And again, I am not understanding what you really need...but here is a start.

Update t
	Set t.Comments = Coalesce(t.Comments, '')
		+ Case 
			When t.Table1 = 'A' Then 'Table1--'
			When t.Table2 = 'B' Then 'Table2--'
			Else Null
		End
    From  #Ttemp as t

---

- will

Answer 3

Basically, I have one table with 100K Rows and 150 different column, 

I generated one report with 80 different small queries now my actual goal is I want to check which query get with rows and if some query overlap then I will know I got these rows with two different querys. 

one simple solution is I can run 80 update commands. I just want to simply this.

Thanks

Answer 4

Russ,

only last thing is I need to run this update multiple times so I want if Comments is not null then update should not update with null. system should keep old values and if there is any new value for comments then its append it.

Thanks for your help

Answer 5

If I understand your problem correct, take a look at the this code.
I did made some comments to help you understand
use TESTDB;
go


--Create a table with table names and the check values to compare to
create table TableCheck
(
	ColName varchar(20)
	,ColCheck varchar(5)
);
insert into dbo.TableCheck
values
 ('Table1', 'A')
,('Table2', 'B')
,('Table3', 'C')
,('Table4', 'D');
-- Add more columns here if your search table has more 

--Create the table to update the data
create table dbo.TestTable
(
	TestTableId int identity(1,1) primary key --Plese note I added a PK 
	,Comments nvarchar(1000)
	,Table1  varchar(5)
	,Table2  varchar(5)
	,Table3  varchar(5)	
	,Table4  varchar(5)	
);
insert into  dbo.TestTable 
values 
(null, 'A', 'B', 'C', 'D'),
(null, 'F', 'B', 'A', 'G'),
(null, 'A', 'S', 'B', 'D'),
(null, 'E', 'A', 'J', 'S');
 
 
 
 
 update dbo.TestTable 
 set Comments = 
 (
	select STUFF 
	(
		(	select '--' as "text()", tc.ColName as "text()"  
					from dbo.TableCheck as tc
					inner join (SELECT name 
								FROM sys.columns 
								WHERE	object_id = OBJECT_ID('dbo.TestTable') 
										and name != 'Comments' 
										and name != 'TestTableId') as c on c.name = tc.ColName
					where (tc.ColName + tc.ColCheck) in (	SELECT  (ColumnName + ColumnCheck) 
															FROM (SELECT *  FROM TestTable as intt where TestTable.TestTableId = intt.TestTableId) p
															UNPIVOT
															(ColumnCheck FOR ColumnName IN (Table1, Table2, Table3, Table4)	)AS unpvt
						)
			for xml path(''))  
	,1, 2, '')
)
 
from dbo.TestTable 
 
 
select * from dbo.TestTable 

Here is the result:
TestTableId    Comments                          Table1    Table2    Table3    Table4
1              Table1--Table2--Table3--Table4    A         B         C         D
2              Table2                            F         B         A         G
3              Table1--Table4                    A         S         B         D
4              NULL                              E         A         J         S

Some references I went:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/1cd5405f-9da7-49fb-a753-06ea2a968735
http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx

Nice coding...

Answer 6

This is designed so that if there already is a comment then that comment is appended to.

declare @t table (Comments varchar(100), Table1 char(1)
, Table2 char(1)
, Table3 char(1)
, Table4 char(1)
)
insert @t  
 values (null, 'A', 'B', 'C','D'),
 (null, 'F', 'B', 'A','G'),
 (null, 'A', 'S', 'B','D'),
 (null, 'E', 'A', 'J','S')
, ('old comment', 'E', 'A', 'J','S')
,  ('Another comment', 'F', 'B', 'A','G') 
;
update @t 
  set Comments = Nullif( isnull(Comments + '--' , '') +
    stuff( 
     isnull ('--' + case when Table1 = 'A' then 'Table1' else null end, '')
     + isnull ('--' + case when Table2 = 'B' then 'Table2' else null end, '')
     + isnull ('--' + case when Table3 = 'C' then 'Table3' else null end, '')
     + isnull ('--' + case when Table4 = 'D' then 'Table4' else null end, '')
	 , 1,  2 , '')
  , '');

  select * from @t

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 7

Russ,

still same issue. I added one more update and you can see the issue. As I mentioned in my scenario I need to run multiple times to check different cases.

Thanks

declare @t table (Comments varchar(100), Table1 char(1)
, Table2 char(1)
, Table3 char(1)
, Table4 char(1)
)
insert @t  
 values (null, 'A', 'B', 'C','D'),
 (null, 'F', 'B', 'A','G'),
 (null, 'A', 'S', 'B','D'),
 (null, 'E', 'A', 'J','S')
, ('old comment', 'E', 'A', 'J','S')
,  ('Another comment', 'F', 'B', 'A','G') 
;
update @t 
  set Comments = Nullif( isnull(Comments + '--' , '') +
    stuff( 
     isnull ('--' + case when Table1 = 'A' then 'Table1' else null end, '')
     + isnull ('--' + case when Table2 = 'B' then 'Table2' else null end, '')
     + isnull ('--' + case when Table3 = 'C' then 'Table3' else null end, '')
     + isnull ('--' + case when Table4 = 'D' then 'Table4' else null end, '')
	 , 1,  2 , '')
  , '');


update @t 
  set Comments = Nullif( isnull(Comments + '--' , '') +
    stuff( 
     isnull ('--' + case when Table1 = 'Z' then 'Table1' else null end, '')
     + isnull ('--' + case when Table2 = 'Y' then 'Table2' else null end, '')
     + isnull ('--' + case when Table3 = 'J' then 'Table3' else null end, '')
     + isnull ('--' + case when Table4 = 'U' then 'Table4' else null end, '')
	 , 1,  2 , '')
  , '');
  
  select * from @t

Answer 8

Russ,

any suggestion?

Answer 9

Small change.  Forgot that the stuff would return null as well:

update @t 
  set Comments = Nullif( isnull(Comments + '--' , '') +
    isnull(stuff( 
     isnull ('--' + case when Table1 = 'A' then 'Table1' else null end, '')
     + isnull ('--' + case when Table2 = 'B' then 'Table2' else null end, '')
     + isnull ('--' + case when Table3 = 'C' then 'Table3' else null end, '')
     + isnull ('--' + case when Table4 = 'D' then 'Table4' else null end, '')
	 , 1,  2 , ''), '')
  , '');

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 10

In this case for second update when it updateding column at the end with "--" and If I have 50 to 60 updated in this way first row should become like this

Table1--Table2--Table3--Table4-----------------------------------------------------------------

Thanks