SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Table Mapping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kalaiselvan
Posting Yak Master

India
109 Posts

Posted - 01/23/2013 :  03:18:46  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 01/23/2013 :  04:01:08  Show Profile  Reply with Quote

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


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

India
109 Posts

Posted - 01/23/2013 :  09:53:14  Show Profile  Reply with Quote
Fine it works.. Thanks Visakh...

Regards,
Kalai
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 01/23/2013 :  11:01:08  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000