Select query that matches data with column names

Question

Post reply

Select query that matches data with column names

Viewing 8 posts - 16 through 22 (of 22 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

How's this?

USE tempdb

GO

-- make a temp table

IF object_id('tempdb..#test', 'U') IS NOT NULL DROP TABLE #test;

CREATE TABLE #test (ID INT, name VARCHAR(20), surname VARCHAR(20), age TINYINT);

-- populate it with some test data

INSERT INTO #test VALUES (1, 'John', 'Smith', 20);

-- declare string for the dynamic sql.

DECLARE @sql NVARCHAR(1000);

-- Populate the dynamic sql string.

-- Prefix each column with a +.

-- Delimit each column with a :

-- After each column name, use a ; to separate the value, and get that value

-- you will need to change the OBJECT_ID() call to reference the proper table

-- See http://www.sqlservercentral.com/articles/comma+separated+list/71700/%5D

-- (Creating a comma-separated list (SQL Spackle))

-- for explanation of how STUFF, XML PATH(''), TYPE and .value() works.

SET @sql = 'SELECT ''' + STUFF((

SELECT '+'':' + name + ';''+CONVERT(VARCHAR(100), ' + name + ')'

FROM sys.columns

WHERE object_id = OBJECT_ID('tempdb..#test','U')

ORDER BY column_id

FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,3,'') +

' FROM #test';

-- print and execute the dynamic sql

PRINT @sql;

EXECUTE sp_executesql @sql;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ilker.cikrikcili SSC Eights! Points: 839 More actions · Answer 2

ilker.cikrikcili

SSC Eights!

Points: 839

April 19, 2011 at 9:18 am

#1313436

Yes! That's it!!

Thank you very much 🙂

WayneS SSC Guru Points: 95460 More actions · Answer 3

ilker.cikrikcili (4/19/2011)
Yes! That's it!!
Thank you very much 🙂

You're quite welcome.

Did you read the referenced article, and do you understand everything that is going on? Please don't use it until you can explain to someone else what it is doing!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ilker.cikrikcili SSC Eights! Points: 839 More actions · Answer 4

Yes, you are right.

I haven't read the article yet but will do it.

Your query is clear for me, I've even converted it to query the actual tables in the system.

SET @sql = 'SELECT ''' + STUFF((

SELECT '+'':' + name + ';''+CONVERT(VARCHAR(100), ' + name + ')'

FROM sys.columns

WHERE object_id = OBJECT_ID('RELEASE_TEST..FEED_RATES','U')

ORDER BY column_id

FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,3,'') +

' FROM FEED_RATES where record_name=''AEDDKKCOMP''';

I should run this query in a stored procedure and pass table name and record_name as parameters. I'm new to ms sql server but I guess it'll be trivial.

Thanks again

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Be careful. You may want to be sure to specify the schema_name for the tables, particularly if you have multiple schemas in a database and there may be tables in each with the same name (myschema1.mytable1 and myschema2.mytable1).

ilker.cikrikcili SSC Eights! Points: 839 More actions · Answer 6

Thanks for advice, I'll keep in mind. But for the time being, we have single schema in DB.

ilker.cikrikcili SSC Eights! Points: 839 More actions · Answer 7

Dear all,

This query was working all right until today:

DECLARE @sql NVARCHAR(1000);

SET @sql = 'SELECT ''' + STUFF((

SELECT '+'':' + name + ';''+CONVERT(VARCHAR(100), ' + name + ')'

FROM sys.columns

WHERE object_id = OBJECT_ID('boss..dealpending','U')

ORDER BY column_id

FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,3,'') +

' FROM dealpending where dealid = 2005502';

PRINT @sql;

EXECUTE sp_executesql @sql;

until I started to run it for a table which has more than 50 columns! I cuts the query at the middle like:

SELECT 'dealid;'+CONVERT(VARCHAR(100), dealid)+':dealsetid;'+....................+CONVERT(VARCHAR(100), dealsetid)+':dealsetnumber;'+CONVERT(VA

therefore I'm getting invalid syntax error. This is just because this query exceeds the max varchar size: varchar(max)

I tried to use other data types like text or ntext to see if I can fit longer queries in it but I got "The data type 'text' used in the VALUE method is invalid" error.

What can I use insted of varchar(max) here?

Thanks

ilker.cikrikcili SSC Eights! Points: 839 More actions · Answer 8

Opps, I realised that it's not because of varchar(max) but DECLARE @sql NVARCHAR(1000);

I created another topic since this problem is not related to current topic.

http://www.sqlservercentral.com/Forums/Topic1106826-391-1.aspx