September 18, 2012 at 12:35 pm
Hi,
I have the following table :-
CREATE TABLE TestData (ID INT , Attr1 int , Attr2 float , Attr3 Varchar (5))
GO
INSERT INTO TestData VALUES (1,1,100,'abc')
GO
INSERT INTO TestData VALUES (1,1,400,'abc')
GO
INSERT INTO TestData VALUES (1,1,100,'abc')
GO
INSERT INTO TestData VALUES (2,3,500,'abc1')
GO
INSERT INTO TestData VALUES (2,4,500,'abc2')
GO
INSERT INTO TestData VALUES (2,2,500,'abc1')
GO
-----------------------------------------
Desired output:-
(ID,Attr1,Attr2,Attr3)
CREATE TABLE TestOutput (ID INT , Attr1 int , Attr2 float , Attr3 Varchar (5))
GO
INSERT INTO TestOutput VALUES (1,1,NULL,'abc')
GO
INSERT INTO TestOutput VALUES (2,NULL,500,NULL)
GO
----------------------------------------
For a given ID : If the value of any of the Attribute ;
Attr1 or Attr2 or Attr3...n is inconsistent ==> Make that particular attribute NULL
Attr1 or Att2 or Att3...n is consistent == > Populate the consistent value.
-----
Please suggest How do I automate process to fetch Many Attributes(repetative ID's) to Single ID with Attribute value either Known/NULL.
PS: There can be n attributes
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
September 18, 2012 at 12:44 pm
Like this?
SELECT TD.ID
,Attrib1 = CASE WHEN MIN(TD.Attr1) <> MAX(TD.Attr1) THEN NULL
ELSE MIN(TD.Attr1)
END
,Attrib2 = CASE WHEN MIN(TD.Attr2) <> MAX(TD.Attr2) THEN NULL
ELSE MIN(TD.Attr2)
END
,Attrib3 = CASE WHEN MIN(TD.Attr3) <> MAX(TD.Attr3) THEN NULL
ELSE MIN(TD.Attr3)
END
FROM TestData TD
GROUP BY TD.ID
To make this dynamic, you will have to rely on dynamic T-SQL with the base query as above.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply