SQL Server Performance Forum – Threads Archive

Query

Hi, The following is the o/p of this query.
SELECT BUS_TYPES,DUNS,LEGAL_BUS_NAME,ANNUAL_REVENUE,REGISTRATION_DATE,RENEWAL_DATE,CORPORATE_URL,PARENT_DUNS_NUMBER,AVG_NUMBER_OF_EMPLOYEES,COUNTRY_OF_INC
FROM lkup_ccr
WHERE DUNS IN (‘030188288′,’035940881′,’051265528′,’063730261′,’072406150′,’098329527′,’106578164′,’119699460′,’121205348′,’144894516′,’145310041′,’145859674′,’146453258′,’167084818′,’173628959′,’178044939′,’193792541′,’780230975′,’797058732′,’827502915′,’831457437′,’932797814′,’964277128’) BUS_TYPES DUNS LEGAL_BUS_NAME
——————– ——— ——————————————–
XXVWXY 030188288 HANNAHVILLE INDIAN COMMUNITY
VW12238BA8NBXY 035940881 SAN CARLOS APACHE TRIBAL COUNCIL
VW2377A8NBXY 051265528 TULE RIVER INDIAN HEALTH CENTER, INC
VW3I8BHS 063730261 CROW TRIBE OF INDIANS
VW3IOW 072406150 MUSCOGEE CREEK NATION
VW2R3INGXY 098329527 KIOWA TRIBE OF OKLAHOMA
VW3IXY 106578164 FORT PECK ASSINIBOINE & SIOUX TRIBES INC
VWXY 119699460 PUEBLO OF JEMEZ
VWXY 121205348 PRAIRIE ISLAND TRIBAL COUNCIL
VWXY 144894516 PAWNEE NATION OF OKLAHOMA
VW3IXY 145310041 QUAPAW TRIBE OF OKLAHOMA
VW23NBXY 145859674 SANTEE SIOUX TRIBE OF NEBRASKA Now the first column bus_types has valid 2 position text character like XX,VW,XY for each duns…These are basically column values like XX is one column,VW another and so on. Now i want a query to separate the 2 position text character for all the duns listed and then turn them into columns(Pivot query)

---

What is the exact problem with the query? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Hi, Sorry for the late response. If you look at the table, the first column bus_types,second is DUNS,third is Legal Business Name. Now BUS_TYPES has a list of codes like ‘XXVWXY’,’VW12238BA8NBXY’,’VW2377A8NBXY’ and so on. These codes are nothing but 2 digit valid codes. Like XX-Asian Am Owned Business,VW-American Owned Business,XY-Women Owned Business, 12- State Govt,23-Tribal Govt and so on. Now i have to decode these codes.For eg :’VW12238BA8NBXY’,I have to decode it into VW,12,23,8B,A8,NB,XY. After decoding I have convert them into columns.Like VW 12 23 8B A8
abc State Govt Tribal Govt The condition is the codes get selected from the given list of DUNS numbers. Thanks,
Keerthi Koli

---

Look for LEFT(column_name,2) function in BOL and then join this 2chars derived from left to your table where definition of xx,vw is stored.
Else write your case logic to get the bus_typeName

---

]]>