Pivot Query With String value

Question

Dear All,

I am Having following 

Select Position , Name from Mytable

Result is as

Position     Name

1                X

2                y

3               Z

3               A

4               B

I want below result

       1              2               3              4

       X              Y               Z              B             

       X              Y               A              B

Thanks in Advance

Ashish Gupte

---

Ashish Gupte

Answer 1

SELECT *

FROM 

(SELECT POSITION, NAME

FROM #TABLE ) AS Source

PIVOT

(MAX(NAME) FOR POSITION IN ([1],[2],[3],[4])) AS PVT;

---

ANK HIT - if reply helps, please mark it as ANSWER or helpful post

Answer 2

Hi ANK HIT

Before Posting in Forum i tried this but i am getting only one Row

1              2               3              4    

X              Y               Z              B 

Ashish Gupte

---

Ashish Gupte

Answer 3

My Actual Query is

Select empID , firstName,  jobTitle ,position , manager   from Table1

by that I am getting following result

empID	firstName		jobTitle		position	manager
1		Viral			Director			4		NULL
2		Ravi			Regional Manager	3		1
3		Shabil		Area Manager		2		2
4		Ramesh		Sales Engineer		1		3
5		Kalpesh		Regional Manager	3		1
6		Vikas		Area Manager		2		5
7		Pradeep		Manager			5		6
8		Dharmendra	Sales Engineer		1		7

and I want as

Director	Regional Manager		Area Manager		Manager		Sales Engineer
Viral		Ravi					Shabil						Ramesh
Viral		Kalpesh				Vikas			Pradeep		Dharmendra

Kindly Help

Ashish Gupte

---

Ashish Gupte

Answer 4

Hi Aashu,

Can you please clarify the logic behind your expected output. I basically want to know the logic behind the combination of employees. For e.g., on what basis have you put Ravi (Regional Manager) with Shabil (Area Manager) & not with Vikas  (Area Manager).

I am sorry, but I failed to make it out.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 5

HI Vinay ,

It is Just an Hierarrchy of the Employee in the company and the post is Vacate where there is NULL in Jobtitle

Ashish gupte

---

Ashish Gupte

Answer 6

That is correct. But how would we identify the correct hierarchy? Is there any base to do that from the data you have shared?

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 7

Dear Vinay,

There are specific front END through which we are Maintaining the Employee Master and the Salary of the Employee if somebody is Leaving the Job we are maintaining the record also. It is Complete HRMS Software. and the Backend is SQL Database . Hierrarchy  is Base on the Either POSITION or JOB Title . I think the problem in my Pivot Query is

ECT *
 
FROM 
 
(SELECT POSITION, NAME
 
FROM #TABLE ) AS Source
 
PIVOT
 
(MAX(NAME) FOR POSITION IN ([1],[2],[3],[4])) AS PVT;

the MAX (Name) please suggest the Changes

Ashish Gupte

---

Ashish Gupte

Answer 8

You need to add another artificial column to make sure you have an extra row, the simplest way is to use ROW_NUMBER() function in your source for the PIVOT.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

Can you please give me asn Example

---

Ashish Gupte

Answer 10

;with cte as (select Position, Name, row_number() over (partition by Position order by Name DESC) as Rn from myTable)

select [1],[2],[3],[4] from cte PIVOT (max(Name) for Position IN ([1],[2],[3],[4])) pvt

order by Rn

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog