Help with SELECT please. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with SELECT please.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-11-24 : 11:03:10

SQL 2005.
I have a table store the state name and I want to convert into column. Please see the result below.
Thanks in advance.

IF OBJECT_ID('Tempdb.dbo.#License', 'u') IS NOT NULL
DROP TABLE #License
GO
CREATE TABLE #License
(
LicenseId INT NULL,
LicenseState VARCHAR(100) NULL
)
go

INSERT #License VALUES (1, 'AK, CA, FL, LA, NJ')
INSERT #License VALUES (2, 'CA, DE, AZ, LA, NJ, NM')
INSERT #License VALUES (3, 'CA, NY, NV, PA, OH, ND, MS, VT, WI')
INSERT #License VALUES (4, 'MI, CO, FL, AL')
INSERT #License VALUES (5, 'KY, MO, IA, AK, NV')
INSERT #License VALUES (6, 'NH, MT, OR')
INSERT #License VALUES (7, 'GA, IA')
INSERT #License VALUES (8, 'CA, NE, OH, NC, WI')
go

SELECT *
FROM #License;

-- Result want:

LicenseId LicenseState
---------- ------------
1 AK
1 CA
1 FL
1 LA
1 NJ
2 CA
2 DE
2 AZ
2 LA
2 NJ
2 NM
3 ...
...
8 CA
8 NE
8 OH
8 NC
8 WI

StateId StateCd StateName
----------- ------- ----------------------
2 AK Alaska
1 AL Alabama
3 AZ Arizona
4 AR Arkansas
5 CA California
6 CO Colorado
7 CT Connecticut
9 DC District of Columbia
8 DE Delaware
10 FL Florida
12 GA Georgia
11 HI Hawaii
16 IA Iowa
13 ID Idaho
14 IL Illinois
15 IN Indiana
17 KS Kansas
18 KY Kentucky
19 LA Louisiana
22 MA Massachusetts
21 MD MaryLand
20 ME Maine
23 MI Michigan
24 MN Minnesota
26 MO Missouri
25 MS Mississippi
27 MT Montana
34 NC North Carolina
35 ND North Dakota
28 NE Nebraska
30 NH New Hampshire
31 NJ New Jersey
32 NM New Mexico
29 NV Nevada
33 NY New York
36 OH Ohio
37 OK Oklahoma
38 OR Oregon
39 PA Pennsylvania
40 RI Rhode Island
41 SC South Carolina
42 SD South Dakota
43 TN Tennessee
44 TX Texas
45 UT Utah
47 VA Virginia
46 VT Vermont
48 WA Washington
50 WI Wisconsin
49 WV West Virginia
51 WY Wyoming

(51 row(s) affected)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-24 : 15:42:11

one good way is to use a table-valued function to parse a delimited list. assuming you have function called [fnParseCSV] you can call it like this:


SELECT l.licenseid, ca.val
FROM #License l
cross apply dbo.fnParseCSV(LicenseState, ',') ca

Search here for "CSV", or "Split Functions"

EDIT:
of course the best way is to change you model so you store one value per row.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources