Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Table Mapping

Author  Topic 

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-01-23 : 03:18:46
Hi,
Need a Query to Built Mapping table from the Entire Table Dump.

TABLEA:

[ID] [ITEMID] [SUBITEM] [FLAG]
1 101 Chain A
2 101 Ring B
3 101 Bracelet C
4 102 S.Chain A
5 102 Leg Chain D
6 102 S.Bracelet C
7 103 Diamond B
8 103 Platinum -
9 103 D.Chain A
10 103 P.LegChain D


From the Above table need to get the Mapped Data Where Flag is not Empty...

TABLE MAPPED:
[Flag] [ITEM-SUBITEM]
A 101-Chain,102-S.Chain,103-D.Chain
B 101-Ring,103-Diamond
C 101-Bracelet,102-S.Bracelet
D 102-Leg Chain,103-P.LegChain

Here the for Each Flag ITEM and SUBITEM Mapped is taken with Comma Seperated. Please found me a Solution to get this table frm above table.


Regards,
Kalaiselvan R


Regards,
Kalai

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 04:01:08
[code]
SELECT Flag,
STUFF((SELECT ',' + CAST([ITEMID] AS varchar(5)) + '-' + [SUBITEM]
FROM TABLEA
WHERE Flag = t.Flag
ORDER BY [ITEMID]
FOR XML PATH('')),1,1,'') AS [ITEM-SUBITEM]
FROM (SELECT DISTINCT Flag FROM TABLEA WHERE Flag <> '-')t
[/code]

once you happy with above code use it in insert as

INSERT INTO [TABLE MAPPED]
the above select query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2013-01-23 : 09:53:14
Fine it works.. Thanks Visakh...

Regards,
Kalai
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-23 : 11:01:08
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -