need help in transpose rows to columns in SQL

i have no clue how to do this: i need to obtain ljob and descript cformfldid 125, 126, and 127 where the ljob = 86252 (just an example ljob).

The data looks like this:

82652    125    100
82652    126    postage description
82652    127    50

I know that it involves a subquery, but I haven't been able to write one that works. Can anyone help?
kennethmurrahAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Based on the text in this question...
SELECT middle_column FROM your_table WHERE left_column = 86252

Open in new window


I'm guessing you'll need to provide a more detailed question in order to get what you're after.
Spelling out the column names to the data mockup would help.
Vitor MontalvãoMSSQL Senior EngineerCommented:
By the sample you posted you don't even need a subquery:
SELECT ljob, descript, cformfldid
FROM YourTableName
WHERE ljob=82652

Open in new window

kennethmurrahAuthor Commented:
ljob         cformfldid    descript
82652    125                 100
82652    126                 postage description
82652    127                 50

Well, I want to be able to get the data in Excel, so i need the data i pull to look like this:

82652          100          postage description           50

all in one record so that i can assign the values of 125, 126 and 127 to different cells in my spreadsheet. Does that make sense at all?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
So, what you really need is to transpose rows into columns. The subquery was only to distract us.
kennethmurrahAuthor Commented:
not to distract y ou, just my failure to understand how to ask  the question
Mike EghtebasDatabase and Application DeveloperCommented:
try:
SELECT Distinct ljob, 
   (
      SELECT descript + ', '
      FROM #TableA
      --WHERE CreatedTime >= A.BeginTime
     --    AND CreatedTime < A.EndTime
      FOR XML PATH('')
   ) AS Notes
FROM #TableA AS A

Open in new window

Vitor MontalvãoMSSQL Senior EngineerCommented:
I'll be back to this question later if no Experts come here to help you.
Anyway this should be a solution with dynamic pivot.
Mike EghtebasDatabase and Application DeveloperCommented:
SELECT Distinct ljob, 
   (
      SELECT descript + ', '
      FROM #TableA
      FOR XML PATH('')
   ) AS Notes
FROM #TableA AS A

Open in new window


Gives:  82652      100, postage description, 50,

using:
create table #TableA(ljob int, cformfldid int, descript varchar(50))
--drop table #TableA
Insert #TableA(ljob, cformfldid, descript) values
(82652, 125, '100' ),
(82652, 126, 'postage description' ),
(82652, 127, '50' )


Select * from #TableA;

Open in new window


I will add another ljob to this table to test it. brb

Mike
Vitor MontalvãoMSSQL Senior EngineerCommented:
I imagine that OP wants a solution that gives:

ljob              125         126                                        127
82652          100          postage description           50
Mike EghtebasDatabase and Application DeveloperCommented:
Hi Vitor,

Possibly OP is asking for:

82652      100, postage description, 50,
82653      200, postage description2, 53,

I added the second line to illustrate. The author needs to tell us what is needed.

Mike
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sure. If he wants all in one string, your solution is fine (just need to remove the last comma btw).
Mike EghtebasDatabase and Application DeveloperCommented:
If a result like shown below is what you want try:

SELECT distinct ljob, 
   (
      SELECT descript + '  '
      FROM #TableA
	  WHERE ljob = A.ljob
      FOR XML PATH('')
   ) AS Notes
FROM #TableA AS A

Open in new window


ljob          Notes 
82652	100  postage description  50  
82653	200  postage description2  53

Open in new window

 

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.