How to generate following output in SQL

Question

0.00/5 (No votes)

See more:

I have table which gives records as below
Select * From EMP
EMPID  VehicalID
1	AB-1
1	CD-1
1	DE-1
2	XY-1
2       PQ-2
---================================================================================---
I want output like this 
EPID              Veh1             Veh2             veh3
1                 AB-1             CD-1             DE-1
2                 XY-1             PQ-2

Posted 9-May-13 21:22pm

koolprasad2003

Updated 9-May-13 21:23pm

v3

Add a Solution

Comments

gvprabu 10-May-13 3:27am

How many Vehicle IDs are there for single Emp ID? then only u can frame the Output.

koolprasad2003 10-May-13 3:31am

Basically Vehicle ID is dummy column name you can keep what ever you want. it's not mandatory to take veh1,veh2 you can take like col1,col2 ..

gvprabu 10-May-13 3:46am

Yes that is not a problem, Maximum how many vehicles will be there for each employees...? Bcos we can't put simple query for this. we can do using SUB Query with ROW_NUMBER() Function

RelicV 10-May-13 3:48am

Have a look at PIVOT option for a solution. That helps a lot

gvprabu 10-May-13 3:57am

mmm okie

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-05-10T00:44:00

[EDIT #1]
There is not possible to achieve that using pivot(s)[^], because aggregate function(s)[^] can be used only on numeric data.
[EDIT]

Try this:

SQL

DECLARE @emp TABLE(EMPID INT, VehicalID NVARCHAR(30))

INSERT INTO @emp (EMPID, VehicalID)
VALUES(1, 'AB-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(1, 'CD-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(1, 'DE-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(2, 'XY-1')
INSERT INTO @emp (EMPID, VehicalID)
VALUES(2, 'PQ-2')

DECLARE @tmp TABLE (CID INT IDENTITY(1,1), EMPID INT, VehNo INT, VehicalID NVARCHAR(30))

INSERT INTO @tmp (EMPID, VehNo, VehicalID)
SELECT EMPID, ROW_NUMBER() OVER(PARTITION BY EMPID ORDER BY VehicalID) AS VehNo, VehicalID
FROM @emp

SELECT DISTINCT total.EMPID, t1.VehicalID AS Veh1, t2.VehicalID AS Veh2, t3.VehicalID AS Veh3
FROM @tmp AS total
    LEFT JOIN @tmp AS t1 ON total.EMPID = t1.EMPID AND t1.VehNo = 1
    LEFT JOIN @tmp AS t2 ON total.EMPID = t2.EMPID AND t2.VehNo = 2
    LEFT JOIN @tmp AS t3 ON total.EMPID = t3.EMPID AND t3.VehNo = 3

Returns:

EMPID   Veh1    Veh2    Veh3
1	AB-1	CD-1	DE-1
2	PQ-2	XY-1	NULL