• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

Select column name as prifix to row values

Hi all

I have a customer table below.
I need the result to be like the values in table customers result below.

I.e I need to append all the result set with column Name as prefix..
How can I achive this?

Thanks in Advance

Customers Resultcustomers table
0
ZURINET
Asked:
ZURINET
1 Solution
 
Kelvin SparksCommented:
You'll need to run a query here. Something like

SELECT 'CustomerID' & CustomerID, 'StoeID' & StoreID,......
FROM yourtable


Kelvin
0
 
sognoctCommented:
you can use string concatenation

example
select
'[customerID].&[' + convert(nvarchar,CustomerID) + ']' as customerID,
'[storeID].&[' + convert(nvarchar,storeID) + ']' as storeID,
...etc ...
FROM tablename
0
 
mbizupCommented:
Make sure ALL of the fields you are updating are defined as text in the underlying table and Try this in VBA:

Sub DoThis()
dim rs as DAO.recordset
SET rs = CurrentDB.OpenRecordset
if rs.recordcount = 0 then Exit Sub
do until rs.eof
    rs.Edit
    rs!CustomerID = "[CustomerID].[" & CustomerID & "]"
    rs!StoreID= "[StoreID].[" & StoreID & "]"
    rs!AccountNumber = "[AccountNumber].[" & AccountNumber & "]"
    rs.Update
    rs.MoveNext
Loop
rs.close
set rs = nothing

Open in new window


EDIT:

Sorry - I though this was posted in the Access zone... This assumes you're working with an Access interface.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
harshada_sonawaneCommented:
u can try this

SELECT '[id].&'+ '['+ convert(varchar,id) +']' as id from customer

same way for all columns
0
 
ZURINETAuthor Commented:
Hi Hars..
Thanks for the great answer!
0
 
mbizupCommented:
ZURINET,

Did you see the earlier post from 'sognoct' at http:#a38809246 ?

Unless I'm missing something,  the answer you accepted from harshada_sonawane is identical to that earlier response...
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now