Need help T-SQL convert column to row.

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)

Need help T-SQL convert column to row.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-06-03 : 11:09:26

I have MS Access table and need to convert the columns to rows. There is 50 states (column names) in #accessTable. Also,
I only want to convert these columns which has value = 1 match with #StateCode and return stateid.
Below is the business rule and desired results. SQL 2005.

Thank you so much in advance.

IF OBJECT_ID('Tempdb.dbo.#AccessTable ', 'u') IS NOT NULL
DROP TABLE #AccessTable
GO

CREATE TABLE #AccessTable
(
CustId INT NULL,
AL TINYINT NULL,
AK TINYINT NULL,
AZ TINYINT NULL,
AR TINYINT NULL,
CA TINYINT NULL,
CO TINYINT NULL
)
GO

INSERT INTO #AccessTable (CustId, AL, AK, AZ, AR, CA, CO) VALUES (7814, 1, 1, 0, 1, 1, 0)
INSERT INTO #AccessTable (CustId, AL, AK, AZ, AR, CA, CO) VALUES (8947, 1, 1, 1, 0, 1, 1)
INSERT INTO #AccessTable (CustId, AL, AK, AZ, AR, CA, CO) VALUES (9871, 1, 0, 1, 0, 0, 1)
GO

SELECT *
FROM #AccessTable;
go

CustId AL AK AZ AR CA CO
----------- ---- ---- ---- ---- ---- ----
7814 1 1 0 1 1 0
8947 1 1 1 0 1 1
9871 1 0 1 0 0 1

IF OBJECT_ID('Tempdb.dbo.#StateCode ', 'u') IS NOT NULL
DROP TABLE #StateCode
GO

CREATE TABLE #StateCode
(
StateId INT NULL,
StateCd CHAR(2) NULL
)
GO

INSERT INTO #StateCode VALUES (1, 'AL')
INSERT INTO #StateCode VALUES (2, 'AK')
INSERT INTO #StateCode VALUES (3, 'AZ')
INSERT INTO #StateCode VALUES (4, 'AR')
INSERT INTO #StateCode VALUES (5, 'CA')
INSERT INTO #StateCode VALUES (6, 'CO')
GO

SELECT *
FROM #StateCode;
GO

StateId StateCd
----------- -------
1 AL
2 AK
3 AZ
4 AR
5 CA
6 CO

SELECT *
FROM #AccessTable;
go

-- Business Rule: Convert only these columns = 1 skip 0.

-- Result want:
CustId StateId
------ -------
7814 1
7814 2
7814 4
7814 5

8947 1
8947 2
8947 3
8947 5
8947 6

9871 1
9871 3
9871 6

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-03 : 11:24:28

Try this

select a.CustId,b.StateId from
(
select CustId,States from
(
SELECT * FROM #AccessTable
) p
UNPIVOT
  (Codes for States IN
	(AL,AK,AZ,AR,CA,CO)
) as unpvt
where Codes = 1
) a
inner join #StateCode b on a.States = b.StateCd

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-06-03 : 11:34:47

Thanks so much for helping.

quote:

Originally posted by vijayisonly

Try this

select a.CustId,b.StateId from
(
select CustId,States from
(
SELECT * FROM #AccessTable
) p
UNPIVOT
  (Codes for States IN
	(AL,AK,AZ,AR,CA,CO)
) as unpvt
where Codes = 1
) a
inner join #StateCode b on a.States = b.StateCd

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-03 : 11:36:08

Np. You're welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources