table contains about 200,000 records...... I want a query to get count of each numbers in the "B" section column values where "A" section contains a particular value....... and also the IDs should be in Second Table with Level 1
means if I search for 12.......I want all the "B" section column values of IDs where their A1 or A11 or A12 .... or A11111 contains 12 and that ID should be in second table with level 1
here 12 is there in column A12 of ID 111 and 111 & 114 is there in second table with Level 1.......and A111 of ID 114.....So I want to select count of "B" section values that occured in these IDs 111 and 114
I want something like following:
col1 total
14 2 (because 14 is there in B1 of 111 and B111 of 114)
I have some tips, you could use below query to get all column names which contains "A".
select column_name from information_schema.columns
where table_name = 'First_Table' and
column_name like 'A%'
order by ordinal_position
Then use those column_name in in the main where clause like this, where [+ column_name +] like '%12%' and iD in (select id from second_table where level =1 ) to find out the ID that match those two conditions. use that ID number to show all the values in
B columns ( you could use get all column name query to get all columns named "B") use pivot function to get a table list like
column_name, values
B1 100
B11 14
.....
Then use count() to get the result you need.
itsjp
Member
24 Points
106 Posts
select the count of each column values from the resultant records
Nov 02, 2012 11:56 AM|LINK
hi,
I have following table with more than 200,000 records.
First Table:
ID A1 A11 A12 A111 A12 A121 A122....A11111 B1 B11 B12 B111 B12 B121 B122 B1211 B1212 .......B22222
111 10 3 12 9 3 18 17 100 14 7 9 43 20 34 45 56 11 13
112 15 11 13 3 17 9 13 12 32 38 57 25 40 42 28 34 11 87
114 39 21 20 12 38 22 18 67 92 14 73 64 32 83 11 4 20 123
....
....
....
Second Table:
ID Level
111 1
112 3
111 2
113 2
1114 1
115 3
...
table contains about 200,000 records...... I want a query to get count of each numbers in the "B" section column values where "A" section contains a particular value....... and also the IDs should be in Second Table with Level 1
means if I search for 12.......I want all the "B" section column values of IDs where their A1 or A11 or A12 .... or A11111 contains 12 and that ID should be in second table with level 1
here 12 is there in column A12 of ID 111 and 111 & 114 is there in second table with Level 1.......and A111 of ID 114.....So I want to select count of "B" section values that occured in these IDs 111 and 114
I want something like following:
col1 total
14 2 (because 14 is there in B1 of 111 and B111 of 114)
7 1
9 1
43 1
20 2
34 1
.....
....
123 1
Can anyone please help me....
sumitjain231
Participant
1498 Points
295 Posts
Re: select the count of each column values from the resultant records
Nov 02, 2012 12:19 PM|LINK
How many columns are in the table?
Remember to click Mark as Answer on the post that helps to others.
itsjp
Member
24 Points
106 Posts
Re: select the count of each column values from the resultant records
Nov 02, 2012 12:27 PM|LINK
Around 65 columns and 200,000 rows in that table.........
I have tried with some inner join query , but its taking long time and getting execution time out error on the web page......
If I get a query something is performance effective too........that will be great.............
limno
All-Star
117430 Points
8032 Posts
Moderator
MVP
Re: select the count of each column values from the resultant records
Nov 02, 2012 08:02 PM|LINK
You need to normalize your data in your design before you are stuggling further if you want to work with SQL Server.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
itsjp
Member
24 Points
106 Posts
Re: select the count of each column values from the resultant records
Nov 03, 2012 02:57 AM|LINK
its already in the normalized form and there is no replicas in the ID field....
the 65 column we have is crucial and we cannot get rid of that columns.....the project nature is something like that.......
can anyone has any idea??
Chen Yu - MS...
All-Star
21829 Points
2513 Posts
Microsoft
Re: select the count of each column values from the resultant records
Nov 08, 2012 05:04 AM|LINK
Hi,
I have some tips, you could use below query to get all column names which contains "A".
Then use those column_name in in the main where clause like this, where [+ column_name +] like '%12%' and iD in (select id from second_table where level =1 ) to find out the ID that match those two conditions. use that ID number to show all the values in B columns ( you could use get all column name query to get all columns named "B") use pivot function to get a table list like
column_name, values
B1 100
B11 14
.....
Then use count() to get the result you need.
Pivot: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Thanks.
Feedback to us
Develop and promote your apps in Windows Store