July 3, 2012 at 10:00 am
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
July 3, 2012 at 10:10 am
This is called a cross tab query. Take a look at the links in my signature. Unless you know how many columns each group will have you are looking at dynamic cross tabs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply