asked on Query syntax - PIVOT?
Hello,
I have a tabled defined as:
It contains data collected from a document - identified by doc_id.
For each document there are many rows where each row is a field name and a field value.
If i know that my document has ddt_FieldNames of A, B, C and D how would i write an sql statement to produce all the data in columns for document with doc_id 6?
I am looking for output like:
doc_id A B C D
6 10 66 12 13
where A, B, C, and D have respective ddt_FieldValues of 10, 66, 12 , 13
I have a tabled defined as:
CREATE TABLE [dbo].[DocumentData](
[doc_id] [int] NOT NULL,
[ddt_FieldName] [nvarchar](20) NULL,
[ddt_FieldValue] [nvarchar](100) NULL,
[ddt_Changed] [bit] NULL,
[ddt_ChangedUserId] [int] NULL,
[ddt_ChangedDateTime] [datetime] NULL,
[id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
It contains data collected from a document - identified by doc_id.
For each document there are many rows where each row is a field name and a field value.
If i know that my document has ddt_FieldNames of A, B, C and D how would i write an sql statement to produce all the data in columns for document with doc_id 6?
I am looking for output like:
doc_id A B C D
6 10 66 12 13
where A, B, C, and D have respective ddt_FieldValues of 10, 66, 12 , 13
Microsoft SQL Server 2008SQL
Last Comment
soozh
ASKER
Q1: I suppose the columns (A-D) comes from ddt_FieldNames?
Yes
Q2: Would it always be A, B, C & D? Can there be more names?
No
Q3: Do you need to sum ddt_FieldValues?
No
I just need a row of data for each document.
Thanks
Yes
Q2: Would it always be A, B, C & D? Can there be more names?
No
Q3: Do you need to sum ddt_FieldValues?
No
I just need a row of data for each document.
Thanks
Your response of No to Q2:
No, there can be more names or No, it would always be ABC&D?
Q4: What to do when no row is found for ddt_FieldNames?
Q5: What to do when there us more than one of the same value in ddt_FieldNames?
No, there can be more names or No, it would always be ABC&D?
Q4: What to do when no row is found for ddt_FieldNames?
Q5: What to do when there us more than one of the same value in ddt_FieldNames?
ASKER
It should Always be A B C and D
Q4: return null as the value
Q5: There should only be one row in the table for each document so i expect that i would have to add the document number to the final Query.
Q4: return null as the value
Q5: There should only be one row in the table for each document so i expect that i would have to add the document number to the final Query.
The simplest way to achieve this would be:
select [doc_id]
, CASE [ddt_FieldName] WHEN 'A' THEN [ddt_FieldValue] ELSE 0 END as 'A'
, CASE [ddt_FieldName] WHEN 'B' THEN [ddt_FieldValue] ELSE 0 END as 'B'
, CASE [ddt_FieldName] WHEN 'C' THEN [ddt_FieldValue] ELSE 0 END as 'C'
, CASE [ddt_FieldName] WHEN 'D' THEN [ddt_FieldValue] ELSE 0 END as 'D'
from DocumentData
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
No points please.
Louis01, you forgot the max( ... ) and group by
Louis01, you forgot the max( ... ) and group by
select [doc_id]
, MAX ( CASE [ddt_FieldName] WHEN 'A' THEN [ddt_FieldValue] ELSE 0 END ) as 'A'
, MAX ( CASE [ddt_FieldName] WHEN 'B' THEN [ddt_FieldValue] ELSE 0 END ) as 'B'
, MAX ( CASE [ddt_FieldName] WHEN 'C' THEN [ddt_FieldValue] ELSE 0 END ) as 'C'
, MAX ( CASE [ddt_FieldName] WHEN 'D' THEN [ddt_FieldValue] ELSE 0 END ) as 'D'
from DocumentData
group by [doc_id]
ASKER
The pivot worked. The other solution created many rows. Thanks.
Q2: Would it always be A, B, C & D? Can there be more names?
Q3: Do you need to sum ddt_FieldValues?