News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Name:Alex Dybenko

Location:Moscow, Russia

Wednesday, August 24, 2011

TSQL and xQuery magic

A person from StackOverflow helped me to find simple SQL to put field names and corresponding values of one row into text. If you run SQL below on Northwind database:

select T2.N.value('local-name(.)', 'nvarchar(128)')+': '+   
       T2.N.value('.', 'nvarchar(max)')
from (select *
      from Customers
      Where CustomerID='ALFKI'
      for xml path(''), type) as T1(X)
  cross apply T1.X.nodes('/*') as T2(N)

You will get result as:

CustomerID: ALFKI 
CompanyName: Alfreds Futterkiste
ContactName: Maria Anders
ContactTitle: Sales Representative
Address: Obere Str. 57
City: Berlin
PostalCode: 12209
Country: Germany
Phone: 030-0074321
Fax: 030-0076545

Very simple SQL, although I have to learn what really means the last line.

Here the original discussion: TSQL: Values of all field in a row into one string



Anonymous Cody said...

Very creatiive post

5:23 PM  

Post a Comment

<< Home