Find value of cell from header name and row name (like going down and across)

Question

Post reply

Find value of cell from header name and row name (like going down and across)

PiyaKaGhar

Mr or Mrs. 500

Points: 568
More actions
October 3, 2013 at 3:38 am

#395599

what I want is the value of cell just like where x meets y, going down and across what is the value in that cell.
Thank you for your time

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Can you be a bit more specific on what you want to achieve?

Table DDL, sample data and desired output are appreciated.

Regarding replies: you can edit your subscriptions for each post. Somewhere you can set you want to be notified by email when someone replies.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

PiyaKaGhar Mr or Mrs. 500 Points: 568 More actions · Answer 2

Hi can I insert image to show,

UPDATE [dbo].[pro]
SET [Test] = <Test, nchar(10),>
,[100] = <100, nchar(10),>
,[120] = <120, nchar(10),>
,[130] = <130, nchar(10),>
WHERE <Search Conditions,,>
GO

test 100 120 130

USA 1 4 3

IND 4 2 3

JAP 4 3 1

am trying to get the cell value eg for usa and 120 result should come as 4 but am stuck

create proc [dbo].[proc2] @productname as varchar(20), @measurement as nvarchar (20)
as
begin
select a.*, b.column_name from pro a, INFORMATION_SCHEMA.COLUMNS b
where a.test=@productname and column_name=@measurement and b.table_name='pro'
end
exec dbo.proc2 'india',[120]

not sure how to do . thank you for your help and guidance.

Sonu

Sean Lange SSC Guru Points: 286589 More actions · Answer 3

PiyaKaGhar (10/11/2013)
Hi can I insert image to show,
UPDATE [dbo].[pro]
SET [Test] = <Test, nchar(10),>
,[100] = <100, nchar(10),>
,[120] = <120, nchar(10),>
,[130] = <130, nchar(10),>
WHERE <Search Conditions,,>
GO
test 100 120 130
USA 1 4 3
IND 4 2 3
JAP 4 3 1
am trying to get the cell value eg for usa and 120 result should come as 4 but am stuck
create proc [dbo].[proc2] @productname as varchar(20), @measurement as nvarchar (20)
as
begin
select a.*, b.column_name from pro a, INFORMATION_SCHEMA.COLUMNS b
where a.test=@productname and column_name=@measurement and b.table_name='pro'
end
exec dbo.proc2 'india',[120]
not sure how to do . thank you for your help and guidance.
Sonu

It is extremely unclear what you are trying to do here. Are you saying you want to pass a value for the test column and the column name for the second value???

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

PiyaKaGhar Mr or Mrs. 500 Points: 568 More actions · Answer 4

test - I have given the colum that name -has got the product details. I want to use the cell name within that col for eg India and the the numbers[100],[120] which are alike column name to get details of the cell

if this is not clear, let me gater how you want details so you can help me.

Regards

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

PiyaKaGhar (10/11/2013)
Hi can I insert image to show,
UPDATE [dbo].[pro]
SET [Test] = <Test, nchar(10),>
,[100] = <100, nchar(10),>
,[120] = <120, nchar(10),>
,[130] = <130, nchar(10),>
WHERE <Search Conditions,,>
GO
test 100 120 130
USA 1 4 3
IND 4 2 3
JAP 4 3 1
am trying to get the cell value eg for usa and 120 result should come as 4 but am stuck
create proc [dbo].[proc2] @productname as varchar(20), @measurement as nvarchar (20)
as
begin
select a.*, b.column_name from pro a, INFORMATION_SCHEMA.COLUMNS b
where a.test=@productname and column_name=@measurement and b.table_name='pro'
end
exec dbo.proc2 'india',[120]
not sure how to do . thank you for your help and guidance.
Sonu

You're running into the same problem as another million people or so. Your table isn't normalized. This would be a whole lot easier if it were. In this case, it would end up looking a bit like an EAV table but it would keep you from having to resort to dynamic SQL to do such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)