Solved

TSQL Query

Posted on 2015-01-14
6
58 Views
Last Modified: 2015-01-18
How to write a TSQL to reformt the column as attached file ? Tks
0
Comment
Question by:AXISHK
  • 2
  • 2
  • 2
6 Comments
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
Hi, please post the attachment you refer to in your question. Thanks.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
ALTER TABLE YourTableName
ALTER COLUMN YourColumnName NewDatatype

Beyond that, give us more details, and we'll give you a more detailed answer.
0
 

Author Comment

by:AXISHK
Comment Utility
Attached file for your comment, Tks
TSQL.xlsx
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
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
 

Author Comment

by:AXISHK
Comment Utility
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now