[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

TSQL Query

How to write a TSQL to reformt the column as attached file ? Tks
0
AXISHK
Asked:
AXISHK
  • 2
  • 2
  • 2
1 Solution
 
SimonCommented:
Hi, please post the attachment you refer to in your question. Thanks.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
ALTER TABLE YourTableName
ALTER COLUMN YourColumnName NewDatatype

Beyond that, give us more details, and we'll give you a more detailed answer.
0
 
AXISHKAuthor Commented:
Attached file for your comment, Tks
TSQL.xlsx
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
SimonCommented:
No idea. There's not enough information to determine how to generate your 'after' worksheet from the 'before' worksheet.
Some further data or rules are required I think.
0
 
AXISHKAuthor Commented:
In Before worksheet, STYPE  = 4 will be summarized up. STYPE =0 and STYPE = 1 will be joined respectively. They will be grouped into separated column. Tks
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not entirely sure based on your writeup, but this should get you most of the way there.
IF OBJECT_ID('tempdb..#test') IS NOT NULL
   DROP TABLE #test
GO

CREATE TABLE #test (
   INVENTBATCHID varchar(25), 
   ITEMID2 varchar(10), 
   INVENTDTYPEID varchar(10), 
   STYPE int,
   RESULT varchar(10)) 

insert into #test (	INVENTBATCHID, ITEMID2, INVENTDTYPEID, STYPE, RESULT) 
values 
   ('P000160279', 'N02927', '18KT', 	4	, '1.08'), 
   ('P000160279', 'N02927', '18KT', 	0	, '105DFA0.57'), 
   ('P000160279', 'N02927', '18KT', 	0	, '19DFB0.32'), 
   ('P000160279', 'N02927', '18KT', 	4	, '17.21'), 
   ('P000160279', 'N02927', '18KT', 	1	, '6XAM3.71'), 
   ('P000160279', 'N02927', '18KT', 	1	, '3XBT3.13'), 
   ('P000160279', 'N02927', '18KT', 	1	, '3XCQ2.16'), 
   ('P000160279', 'N02927', '18KT', 	1	, '8XCT5.47'), 
   ('P000160279', 'N02927', '18KT', 	1	, '3XGA1.93'), 
   ('P000160279', 'N02927', '18KT', 	1	, '2XLB1.74'), 
   ('P000160279', 'N02927', '18KT', 	1	, '3XLQ1.47'), 
   ('P000160279', 'N02927', '18KT', 	1	, '8XSQ4.88')

SELECT DISTINCT INVENTBATCHID, ITEMID2, INVENTDTYPEID,
   STUFF(
   (
      SELECT '  ' + CAST(RESULT AS VARCHAR)
      FROM #test AS t2
      WHERE t2.INVENTBATCHID = t.INVENTBATCHID AND t2.ITEMID2 = t.ITEMID2 AND  t2.INVENTDTYPEID = t.INVENTDTYPEID 
         AND STYPE = 0
      FOR XML PATH('')
   ), 1, 1, '') AS STYPE0,
   STUFF(
   (
      SELECT '  ' + CAST(RESULT AS VARCHAR)
      FROM #test AS t2
      WHERE t2.INVENTBATCHID = t.INVENTBATCHID AND t2.ITEMID2 = t.ITEMID2 AND  t2.INVENTDTYPEID = t.INVENTDTYPEID 
         AND STYPE <> 0
      FOR XML PATH('')
   ), 1, 1, '') AS STYPE1
FROM #test AS t

Open in new window


Also, just to set expectations, 'write a query to make this set of data show as that set of data' is a good start, but it helps to provide details as to how you want the data transformed.  If experts have to ask multiple clarifying questions there's a risk that they will lose interest and not answer the question.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now