TSQL Pivot

I have a table that contains the following rows:

doc_id	ddt_FieldName	ddt_FieldValue	ddt_Changed	ddt_ChangedDateTime
744	Name		Richard Bobby	NULL		NULL
744	Year		54		NULL		NULL
744	Man		1		NULL		NULL
744	Gender		1		NULL		NULL
744	Woman		false		NULL		NULL
744	ProjectIDNumber	112		1		2013-10-19 21:31:39.057
744	FormDate			NULL		NULL
744	Enhet		NULL		NULL		NULL

Open in new window


and i am using the following pviot statement:

select * from DocumentData PIVOT (max(ddt_FieldValue) FOR [ddt_FieldName] 
  IN ([Name], [Year], [Gender], [ProjectIDNumber], [FormDate], [Enhet], [Sjukgymnast])) AS pvt where doc_id = 744

Open in new window


I have just added the columns ddt_Changed and ddt_ChangedDateTime.  

The row with Project id number has values in these columns.

Now the pivot is giving me two rows instead of one.

Any ideas why?
soozhCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
You need to limit the data retrieved to just that required by the pivot
This result:
| DOC_ID |          NAME | YEAR | GENDER | PROJECTIDNUMBER | FORMDATE |  ENHET | SJUKGYMNAST |
|--------|---------------|------|--------|-----------------|----------|--------|-------------|
|    744 | Richard Bobby |   54 |      1 |             112 |   (null) | (null) |      (null) |

Open in new window

Produced by this query:
SELECT *
FROM (
       SELECT doc_id, ddt_FieldName, ddt_FieldValue
       FROM DocumentData
       WHERE ddt_FieldName IN (
                                 'Name', 'Year', 'Gender', 'ProjectIDNumber'
                               , 'FormDate', 'Enhet', 'Sjukgymnast'
                              )
        AND doc_id = 744
      ) AS dd
PIVOT (max(ddt_FieldValue) FOR [ddt_FieldName]
  IN ([Name], [Year], [Gender], [ProjectIDNumber], [FormDate], [Enhet], [Sjukgymnast])) AS pvt

;

/* ther details */
CREATE TABLE DocumentData
    ([doc_id] int, [ddt_FieldName] varchar(15), [ddt_FieldValue] varchar(13), [ddt_Changed] varchar(4), [ddt_ChangedDateTime] datetime)
;
    
INSERT INTO DocumentData
    ([doc_id], [ddt_FieldName], [ddt_FieldValue], [ddt_Changed], [ddt_ChangedDateTime])
VALUES
    (744, 'Name', 'Richard Bobby', NULL, NULL),
    (744, 'Year', '54', NULL, NULL),
    (744, 'Man', '1', NULL, NULL),
    (744, 'Gender', '1', NULL, NULL),
    (744, 'Woman', 'false', NULL, NULL),
    (744, 'ProjectIDNumber', '112', '1', '2013-10-19 21:31:39'),
    (744, 'FormDate', NULL, NULL, NULL),
    (744, 'Enhet', NULL, NULL, NULL)
;
: http://sqlfiddle.com/#!3/42c30/7 

Open in new window

0
Tony303Commented:
How about a max on every field???

 SELECT MAX(Doc_id) AS Doc_id,MAX(ddt_Changed) AS ddt_Changed, MAX(ddt_ChangedDateTime) AS ddt_ChangedDateTime,
 MAX(Name) AS Name, MAX(YEAR) AS YEAR, MAX(Gender) AS Gender ,MAX(ProjectIDNumber) AS ProjectIDNumber,
 MAX([FormDate]) AS FormDate, MAX([Enhet]) AS Enhet
 from DocumentData  
 PIVOT (max(ddt_FieldValue) FOR [ddt_FieldName] 
  IN ([Name], [Year], [Gender], [ProjectIDNumber], [FormDate], [Enhet])) AS pvt 

Open in new window


Paul must be as bored as me, connected to EE on a Sunday night.....sigh!!!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.