| Author |
 Topic  |
|
|
HelalM
Starting Member
USA
19 Posts |
 Posted - 07/02/2012 : 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 |
Edited by - HelalM on 07/03/2012 13:05:52
|
|
|
yosiasz
Flowing Fount of Yak Knowledge
USA
1608 Posts |
Posted - 07/03/2012 : 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
USA
19 Posts |
Posted - 07/03/2012 : 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 |
|
|
| |
Topic |
|
|
|
combine multiple rows of data on one line
