Solved

how we put this query together

Posted on 2013-01-29
5
291 Views
Last Modified: 2013-02-22
Hi guys, i have a user table,

id    user    
1      bob
2      ted
3      robert

 and this funcion dbo].[Fn_Get] that returns one row of 3 collumns, like this based on the userid.
so if i call the funcion like this : dbo].Fn_Get(1) should return

idl     idb     idc
3        4        5

i want the select to return the users like this.

id    user          idl     idb      idc
1      bob           3        4         5
2      ted            6         3         8
3      robert        4        6        5

so the select query should look like?

select t1.id, t1. from tbuser as t1
0
Comment
Question by:rafaelrgl
5 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 38832494
Not real sure of your question. Are you looking for something like this -
select id, user, dbo.Fn_Get(id) from users
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38832571
I think you need { cross | outer } apply, but not 100% sure.


SELECT
    ut.id, ut.user, fg.idl, fg.idb, fg.idc
FROM dbo.userTable ut
CROSS APPLY dbo.Fn_Get(ut.id) AS fg --Fn_Get is a table-valued function


On SQL 2008, you have some additional options using VALUES() also.
0
 
LVL 1

Author Comment

by:rafaelrgl
ID: 38832666
yes, it's cross aplly, but can we change the function to a procedure. because i can't use newid() on funcion. So can we make the same query you show me but instead of using a function it will be an procedure? can we?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 38832720
No, you can't use a procedure that way.

Why would you need NEWID() in a function?
0
 
LVL 10

Assisted Solution

by:deviprasadg
deviprasadg earned 250 total points
ID: 38834317
In that case add NEWID to the select statement.(select statement with usertable and crossapply on function)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
View SQL 2005 Job package 16 40
convert in derived column 7 27
sql select record as one long string 21 21
sql server service accounts 4 19
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now