How do you select just one cell from one row on one column in a table?

Transact-SQL
1

So this is what I am tryng to do. The goal is to produce a temp table where the columns are numbered 1 to 10.
The following sql statements produces PART of the data that is required content:

select Count() as _Count,RuleName from CompDiffTable group by RuleName order by _Count desc
select Count() as _Count,RuleName INTO #DATA_DrillReport from CompDiffTable group by RuleName order by _Count desc

SELECT * FROM #DATA_DrillReport order by _Count desc

DROP TABLE #DATA_DrillReport

This creates an ordered table where a column of RuleNames ordered the way I want.
Now, lets suppose one of those rule names is GIZANTHAPUS (it is not, but let's just say it is).
So, as I test to get a FileID associated with GIZANTHAPUS, I tried this mishapen SQL query:

SELECT  FileId   FROM CompDiffTable where RuleName = "GIZANTHAPUS"

The syntax is wrong, but you get the idea.
It should have returned a list of FileId's where the RuleName is GIZANTHAPUS
This test was an attempt to step towards createing a query where "GIZANTHAPUS" is replaced by a variable repesenting the RuleName from the first query.
What I really want in the final temp table is all the FileId's that are associated with the RuleNames. That is the content of the final temp table.

I want my final temp table to contain data where the queried RuleNames are are in the column order that they would appear as rows in the query:

SELECT  * FROM #DATA_DrillReport  order by _Count desc

And the rows would contain the FileID's associated that with that RuleName.

I have seen a select statement that produces a table actually have nested select statement for each column. This, I think is what I need to do. This picture shows such a select statement where each column of the temporary table is made by its own select statement. This is what I want to do. The only thing I think I am missing now is how to make each of these nested select statements query just ONE row of the first table I made under the column for RuleName. This pic is just an example of this nested select pattern and now representative of what my query will look like

04
04.PNG1117×242 9.63 KB

2

i am just giving an example

hope it helps
:slight_smile:
:slight_smile:

drop create data 
drop table temp 
go 

create table temp
(
value int ,
) 
go 

insert into temp select 1
insert into temp select 2
insert into temp select 3
insert into temp select 4
insert into temp select 5
insert into temp select 6
insert into temp select 7
insert into temp select 8
insert into temp select 9
insert into temp select 10
go 

select * from temp 
go
SQL .. 
select 'create table temp ( '
union all 
select 'column'+cast( value  as varchar) +' int ,'
from temp 
union all 
select ')'
Result

image

3

this produces a table of just one column and the rows are numbered 1 to 10

4

Please provide sample data with ddl and dml