Solved

TSQL Query

Posted on 2015-01-13
8
92 Views
Last Modified: 2015-01-13
How to write a TSQL to present records in a table as shown in the attached file ?

Tks
TSQL.xlsx
0
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40546162
You may need an AutoNumber to get it in the right order.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40546169
Here you go:

DECLARE @Sample TABLE
    (
      InventBatchID nvarchar(20),
	  ItemID nvarchar(20),
	  TypeID nvarchar(20),
	  SType int,
	  Result nvarchar(20))

INSERT  INTO @Sample
VALUES ('P000042545', '185XA00856', '14KC', 0, '12DFA0.06'),
('P000042545', '185XA00856', '14KC', 0, '6DFC0.17'),
('P000042545', '185XA00856', '14KC', 0, '1DFE0.09'),
('P000042545', '185XA00856', '14KC', 4, '2,83'),
('P000042545', '185XA00856', '14KC', 0, '8DFB0.10'),

('P000160279', '193XN02927', '18KT', 0, '19DFB0.32'),
('P000160279', '193XN02927', '18KT', 4, '17,21'),
('P000160279', '193XN02927', '18KT', 0, '105DFA0.57'),
('P000160279', '193XN02927', '18KT', 4, '1,08'),
('P000160279', '193XN02927', '18KT', 1, '6XAM3.71'),
('P000160279', '193XN02927', '18KT', 1, '3XBT3.13'),
('P000160279', '193XN02927', '18KT', 1, '3XCQ2.16'),
('P000160279', '193XN02927', '18KT', 1, '8XCT5.47'),
('P000160279', '193XN02927', '18KT', 1, '3XGA1.93'),
('P000160279', '193XN02927', '18KT', 1, '2XLB1.74'),
('P000160279', '193XN02927', '18KT', 1, '3XLQ1.47'),
('P000160279', '193XN02927', '18KT', 1, '8XSQ4.88'),

('P000241984', '123XD00160', 'CUW', 4, '1,78'),
('P000241984', '123XD00160', 'CUW', 1, '[TaT')


select InventBatchID, ItemID, TypeID, 
stuff((select ',' + Result from @Sample as T
where T.InventBatchID = S.InventBatchID and T.ItemID = S.ItemID and t.TypeID = s.TypeID and t.SType = s.SType
for xml path('')),1,1,'') as MyResult
from @Sample as S
group by InventBatchID, ItemID, TypeID, SType

Open in new window

0
 

Author Comment

by:AXISHK
ID: 40546186
Can you help me to understand the expression ? What does  the stuff and for xml path mean ?

Tks
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40546194
"Stuff" substitutes one expression for another. Here I am substituting the first character for nothing.

XML PATH exports the results into XML. It is a very big topic - this might help: http://msdn.microsoft.com/en-us/library/ms178107.aspx
0
 

Author Comment

by:AXISHK
ID: 40546447
Test the query, if i omit the 'for xml path('')', it doesn't work. What's its purpose in this scenario ? Why do I need to format the result into XML rather than rowset ?? Tks

Msg 512, Level 16, State 1, Line 33
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

select InventBatchID, ItemID, TypeID,
(select ',' + Result from @Sample as T
where T.InventBatchID = S.InventBatchID and T.ItemID = S.ItemID and t.TypeID = s.TypeID and t.SType = s.SType) as MyResult
from @Sample as S
group by InventBatchID, ItemID, TypeID, SType
Tks
0
 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40546490
Here is your solution...

 SELECT DISTINCT INVENTBATCHID, ITEMID, TYPEID,
 STUFF ((Select ' '+ RESULT From tempExe as p1
 WHERE  p2.INVENTBATCHID=p1.INVENTBATCHID And P2.ITEMID=p1.ITEMID And p2.TYPEID=p1.TYPEID And p2.STYPE=P1.STYPE
 For XML PATH('')),1,1,'') as RResult
 FROM tempExe as p2
 GROUP BY INVENTBATCHID, ITEMID, TYPEID, STYPE
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40546493
for xml path('') allows you to put all of the relevant results on one line.

If it works...
0
 

Author Closing Comment

by:AXISHK
ID: 40548099
Tks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question