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.
| Author |
Topic |
|
HelalM
Starting Member
19 Posts |
 Posted - 2012-07-02 : 21:03:14
|
| I need to get my data organized by pcpname as unique records. Currently my data has multiple records for pcpname like:--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable--===== Create the test table with CREATE TABLE #mytable CREATE TABLE #mytable (pcpname VARCHAR(50),pay2name VARCHAR(50),pay2id VARCHAR(50),memcount int,contract VARCHAR(50));INSERT INTO #mytable VALUES('Smith J', 'HCI1','Q0001',5,'Med Trans 100%')INSERT INTO #mytable VALUES('Smith J', 'HCI2','Q0002',7,'Med Trans 105%')I want to get one line data for the two lines as follow:--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable--===== Create the test table with CREATE TABLE #mytable CREATE TABLE #mytable (pcpname VARCHAR(50),pay2name VARCHAR(50),pay2name1 VARCHAR(50),pay2id VARCHAR(50),pay2id1 VARCHAR(50),memcount int,memcount 1int,contract VARCHAR(50)contract1 VARCHAR(50));INSERT INTO #mytable VALUES('Smith J', 'HCI1','Q0001',5,'Med Trans 100%','HCI2','Q0002',7,'Med Trans 105%')I tried outer apply with XML but got quite confused with getting multiple fields in one line.Thank for your help.HM |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-07-03 : 13:26:20
|
| so are the numbers of columns going to grow exponentially as Smith J has more and more rows in the table?what are you trying to accomplish first of all?<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
HelalM
Starting Member
19 Posts |
Posted - 2012-07-03 : 14:28:22
|
| number of columns are not going to grow exponentially. The max will be 20 columns@pcp. What I am trying to get at is to meet part of report requirements that may not look at smart but it's HAVE TO. Basically, restructuring data from multiple rows into one row@pcp. so, Smith J may have 10 records due to multiple pay2s, memcounts, and contracts. I want to get his 10 records in one row with separate columns for each pay2s, memcoutns, and contracts.HM |
|
|
|
|
|
|
|
|
combine multiple rows of data on one line