SQL Server Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server Error

Author

Topic

leary222
Starting Member

9 Posts

Posted - 2014-12-18 : 16:19:57

[CODE]

SELECT DISTINCT COLUMN_NAME,
(SELECT COUNT(COL1.COLUMN_NAME.VALUE) AS Expr1
FROM dbo.Evaluation_Scores AS Evaluation_Scores_1
WHERE (COL1.COLUMN_NAME.VALUE = 'Development')) AS Development
FROM INFORMATION_SCHEMA.COLUMNS AS COL1
WHERE (TABLE_NAME = N'Evaluation_Scores')
GROUP BY COLUMN_NAME
[\CODE]

Can someone please advise why this throws an error im trying to use the row results from the column called column name as part of a select statement for a sub query but get the error cannot call methods on nvarchar

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-18 : 16:23:26

Please post the error messages.

FWIW you're trying to use a three-part name, 'COL1.COLUMN_NAME.VALUE'. That tells SQL to look in the database COL1 for a table called COLUMN_NAME and retrieve the contents of the column VALUE.

I suspect that's not what you want.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-18 : 16:25:19

You'll need to post your question on a MySql forum as COLUMN_NAME.VALUE is not supported in Microsoft SQL Server, which is what SQLTeam.com is for.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

leary222
Starting Member

9 Posts

Posted - 2014-12-18 : 16:28:46

Hi, that's kind of exactly what I want for example in my results table I want the below

however the error I am getting is ' cannot call methods on nvarchar '

Column_Name , Development
Users , =SELECT COUNT(Users) FROM dbo.Evaluation_Scores AS Evaluation_Scores_1 WHERE (Users = 'Development'))
Data , =SELECT COUNT(Data) FROM dbo.Evaluation_Scores AS Evaluation_Scores_1 WHERE (Data = 'Development'))
Example , =SELECT COUNT(Example) FROM dbo.Evaluation_Scores AS Evaluation_Scores_1 WHERE (Example = 'Development'))

leary222
Starting Member

9 Posts

Posted - 2014-12-18 : 16:31:02

hi it is Microsoft SQL server I am using alongside MS visual studios, that may be why I am having an error? what would the work around on this be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-18 : 16:34:00

This doesn't narrow it down to one table, but I've used this in the past when needing to search lots of columns: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

leary222
Starting Member

9 Posts

Posted - 2014-12-18 : 16:37:21

all I want is to count the related values in another table using the value of Column_name as the parameter for each value

leary222
Starting Member

9 Posts

Posted - 2014-12-18 : 16:41:27

normally a join on the Column name field would be sufficient but I want to use the value which would normally be passed as part of the statement what I have is a table which has a number of columns which the end user will add to during use of the application and I want to be able to get a listing of the count of the amount of times a value is contained in each column of that table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-18 : 16:52:20

I'm not following you. Please post sample data and expected output.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

leary222
Starting Member

9 Posts

Posted - 2014-12-18 : 17:00:18

Sample Table(1)

Columns: Admin , Users , Data
Row1: Development , Development, Yes
Row2: Development , Yes , Yes
Row3: Yes , Development, Development
Row4: Development , Yes , Yes
Row5: No , Yes , Yes

Expected Result
Columns: Column_Name, Count Of Development
Row 1: Admin , 3
Row 2: Users , 2
Row 3: Data , 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-18 : 17:02:43

That's going to be tricky. You're going to need to use dynamic SQL, looping through each column in INFORMATION_SCHEMA.COLUMNS and storing the count. I don't have time to workup a solution, but it isn't just one SELECT query. You'll need a WHILE loop + dynamic SQL, probably a temp table.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

leary222
Starting Member

9 Posts

Posted - 2014-12-18 : 17:05:21

if my subquery could get the column name to be used within it then that would work though but im not sure how to use the value in my sub query, as you pointed out the .value method doesn't work in MS SQL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-18 : 17:06:54

quote:
Originally posted by leary222

the .value method doesn't work in MS SQL

Exactly and that's why this is not a simple thing.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

leary222
Starting Member

9 Posts

Posted - 2014-12-18 : 17:08:39

I see thanks for your help

leary222
Starting Member

9 Posts

Posted - 2014-12-18 : 17:50:18

is anyone able to give me an example of this as I really have no clue?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-18 : 19:13:51

Check out the code in that link I provided. That's the type of stuff you'll need to do. Learn dynamic SQL. Make sure you make use of SELECT/PRINT @sql to debug it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources