combining fields - 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)

combining fields

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2009-06-23 : 08:48:10

Morning All,

i have a table like a matrix, showing code and unit along with it's kp entries.

See table structure below

CREATE TABLE #kpdetail
(
[Code] [int] NULL,
[Unit] [varchar](20) NULL,
[KP1] [varchar](5) NULL,
[Kp2] [varchar](5) NULL,
[Kp3] [varchar](5) NULL,
[Kp4] [varchar](5) NULL,
[KP5] [varchar](5) NULL,
[kp6] [varchar](5) NULL,
[kp7] [varchar](5) NULL,
[kp8] [varchar](5) NULL,
[kp9] [varchar](5) NULL,
[kp10] [varchar](5) NULL,
[kp11] [varchar](5) NULL,
....................................
[kp350] [varchar](5) NULL,
)

so the table appears like this when queried

[Code] [Unit] [KP1] [KP2] [KP3] [KP4] .. [KP390]
100 Legal 4 6 5 8 3
200 Payment null 6 null 8 3

I want to break this table so that i can get the following result

code unit kp
100 Legal 4
100 Legal 6
100 Legal 5
100 Legal 8
..............................
100 Legal 3
200 Payment 6
200 Payment 8
.............................
200 Payment 3

The ... show i can have more than 10 fields.

Am very graceful... thanks all

I sign for fame not for shame but all the same, I sign my name.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-06-23 : 08:54:13

use UNPIVOT

http://msdn.microsoft.com/en-us/library/ms177410.aspx