Need help with the query.........

Question

Post reply

Need help with the query.........

kapil_kk

SSC-Insane

Points: 21316
More actions
January 2, 2014 at 6:30 am

#287774

Hi all,
I have a table like this:
CREATE TABLE [dbo].[Task](
[UnitHistoryTaskId] [int] IDENTITY(1,1) NOT NULL,
[InstanceId] [nchar](3) NOT NULL,
[SiteId] [nchar](3) NOT NULL,
[LocalUnitId] [nvarchar](20) NOT NULL,
[CompletedDate] [datetime] NOT NULL,
[WorkTypeCategoryID] [int] NOT NULL,
[WorkTypeID] [int] NOT NULL,
[LocalTaskNumber] [nchar](8) NULL,
[WorkPerformedCode] [nchar](3) NULL,
[ComponentCode] [nchar](3) NULL,
[StatusFlag] [bit] NOT NULL,
[LocalDateChanged] [datetime] NULL,
[LocalChangedBy] [nvarchar](75) NULL
)
Insert some dumy data into this table:
INSERT INTO TASK
VALUES ('001','001',1,GETDATE(),1,2,'AA','A','B',1,GETDATE(),'kk'),
('001','001',2,GETDATE(),1,3,'AA','A','B',1,GETDATE(),'ABC')
Another table in which data needs to be inserted:
Create table #temp
(
Id int identity(1,1),
RowValue nvarchar(MAX)
)
Desired output:
ID RowValue
1,{
"InstanceId": "001",
"SiteId": "001",
"LocalUnitId": "1",
"CompletedDate": "01-02-2014",
"WorkTypeCategoryID": "1",
"WorkTypeID": "2",
"LocalTaskNumber": "AA",
"WorkPerformedCode": "A",
"ComponentCode": "B",
"StatusFlag": "1",
"LocalDateChanged": "01-02-2014",
"LocalChangedBy": "kk"
}
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

this is what I am doing but stuck at not getting column values:

SELECT STUFF(

(SELECT ',' + '"'+sc.name+ '":' + '"' + column values+ '"'

FROM sys.columns sc

INNER JOIN sys.tables st on sc.object_id = st.object_id

WHERE st.name = 'Task'

FOR XML PATH('')),1,1,'')

from Task U

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

FOR XML PATH is used for concatenating values from different rows - the value you are attempting to assemble is a concatenation of values on the same row. Just use concatenation:

SELECT ID, '{

"InstanceId": "' + CAST(InstanceId AS VARCHAR(10)) + '",

"SiteId": "' + CAST(SiteId AS VARCHAR(10)) + '",

...

You might want to put in a CHAR(10) here and there to get it to look the way you've outlined in your desired results.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

ChrisM@Work (1/2/2014)
FOR XML PATH is used for concatenating values from different rows - the value you are attempting to assemble is a concatenation of values on the same row. Just use concatenation:
SELECT ID, '{
"InstanceId": "' + CAST(InstanceId AS VARCHAR(10)) + '",
"SiteId": "' + CAST(SiteId AS VARCHAR(10)) + '",
...
You might want to put in a CHAR(10) here and there to get it to look the way you've outlined in your desired results.

But table name will changed dynamically as there will be many tables..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

kapil_kk (1/2/2014)
ChrisM@Work (1/2/2014)
FOR XML PATH is used for concatenating values from different rows - the value you are attempting to assemble is a concatenation of values on the same row. Just use concatenation:
SELECT ID, '{
"InstanceId": "' + CAST(InstanceId AS VARCHAR(10)) + '",
"SiteId": "' + CAST(SiteId AS VARCHAR(10)) + '",
...
You might want to put in a CHAR(10) here and there to get it to look the way you've outlined in your desired results.
But table name will changed dynamically as there will be many tables..

The first three posts of this new thread don't appear to be related in any way except for the author. If you can explain what you want to do, it can be done - but for now we've got a rusty car wheel, a piece of lime green chalk and a small fish! How about bring it all together?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

ChrisM@Work (1/2/2014)
kapil_kk (1/2/2014)
ChrisM@Work (1/2/2014)
FOR XML PATH is used for concatenating values from different rows - the value you are attempting to assemble is a concatenation of values on the same row. Just use concatenation:
SELECT ID, '{
"InstanceId": "' + CAST(InstanceId AS VARCHAR(10)) + '",
"SiteId": "' + CAST(SiteId AS VARCHAR(10)) + '",
...
You might want to put in a CHAR(10) here and there to get it to look the way you've outlined in your desired results.
But table name will changed dynamically as there will be many tables..
The first three posts of this new thread don't appear to be related in any way except for the author. If you can explain what you want to do, it can be done - but for now we've got a rusty car wheel, a piece of lime green chalk and a small fish! How about bring it all together?

I apologize Chris....

I will try what you suggested then post here if got any issues 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 6

ChrisM@Work (1/2/2014)
... for now we've got a rusty car wheel, a piece of lime green chalk and a small fish!

No small fish allowed.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

pietlinden SSC Guru Points: 63503 More actions · Answer 7

But what if it's a Babelfish and I need it to speak T-SQL?

Dwain Camps SSC Guru Points: 86948 More actions · Answer 8

pietlinden (1/2/2014)
But what if it's a Babelfish and I need it to speak T-SQL?

You can use:

EXEC sp_translate @SourceLanguage='gibberish', @TargetLanguage='T-SQL'

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

dwain.c (1/2/2014)
pietlinden (1/2/2014)
But what if it's a Babelfish and I need it to speak T-SQL?
You can use:
EXEC sp_translate @SourceLanguage='gibberish', @TargetLanguage='T-SQL'

This comes in very handy when all you've got for a spec is some scribbles on the back of a fag packet.

Having said that, I'm frequently amazed by how some of the folks here can correctly interpret the scantiest and weirdest of OP specs and come up with an accurate solution.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

dwain.c (1/2/2014)
ChrisM@Work (1/2/2014)
... for now we've got a rusty car wheel, a piece of lime green chalk and a small fish!
No small fish allowed.

Missed the prefix "sail" 😛

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden