I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link http://www.sqlservercentral.com/articles/JSON/68128/. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

Thank you so much in advance for your help.

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

• Edited by dvnandover Wednesday, April 29, 2015 7:53 PM

Thank you Patrick Hurst.

Your suggestion answered my second question. I should have figured this one out by my self. I am such a Newbie.

Now my query returned 15 rows for each row in the table. So How do I fix this issue?

• Edited by dvnandover Wednesday, April 29, 2015 8:07 PM

Here is the structure of my Product table (6 columns)
Table Product:
Item1  Item2   Item3  Item4  Item5        Item6
aaaaa  bbbbb   ccccc   ddddd  see below  seebelow
Item5 sample:{"rulesOutputLabel":"lesionGreaterThanOneCmEp","benignFeatures":"no","benignByImagingOrBiopsy":"no","location":"rightLobe","size":10}

output table for select fnSplitJson2(Item5,NULL):
name                                                                           value

rulesOutputLabel                                                  lesionGreaterThanOneCmEp
benignFeatures                                                    no
benignByImagingOrBiopsy                                    no
location                                                               rightLobe
size                                                                     10

Item6 sample:
{"satisfiedContent":"no","satisfiedWording":"yes","preferredContent":"Shorter term f/u because of multiple GGN"}

output table for select fnSplitJson2(Item6,NULL):

name                                                                  value
satisfiedContent                                                   no
satisfiedWording                                                  yes
preferredContent                                                 Shorter term f/u because of multiple GGN

if I execute my original query (calling fnSplitJson2 once) here is what i got:
Item1      item2       Item3    Item4        name                                value
aaaaaa    bbbbbb     cccccc    dddddd     rulesOutLabel                     lesionGreaterThanOneCmEp
aaaaaa    bbbbbb     cccccc    dddddd     benignFeatures                   no
aaaaaa    bbbbbb     cccccc    dddddd     benignByImagingOrBiopsy   no
aaaaaa    bbbbbb     cccccc    dddddd     location                              rightLobe
aaaaaa    bbbbbb     cccccc    dddddd     size                                    10

But i wan to output to look like this (one row):
Item1     item2       Item3      Item4     ruleOutLable                         benignFeatures   
aaaaaa  bbbbbbb     cccccc     dddddd   lesionGreaterThanOneCmEp   no

benignByImagingOrBiopsy  location      size
no                                    rightLobe    10

Edit: Please keep in mind that the JSON data in Item5 and 6 can be varied (key/value pairs changes in size) from one row to the next in the product table.

I hope I explained it clearly enough.

• Edited by dvnandover Thursday, April 30, 2015 4:15 PM

Thank you both Jingyang Li and Patrick  Hurst for your help so far.

While I am trying to adapt your codes to work with my environment and real data I noticed that both of your solutions contained actual column names and it might not work completely with my table since the data from the JSON columns are can be varied between from one row to the next so column names like ruleOutLabel, benignByImagingOrBiopsy are on the first row but might not be there on the second row. Here are sample of Item5 from 3 different rows:

{

"rulesOutputLabel":"cat1NoLungNodulesEp","categoryOfFindings":"cat1NoLungNodules"}

{

"rulesOutputLabel":"shatteredSpleenEp","activeExtravasation":"evidencePseudoaneurysmYes","shatteredSpleenOrvascularInjury":"shatteredSpleen"}

{

"rulesOutputLabel":"gastricEmptyingEp1","pct4Hr":100,"pct2Hr":100,"pct1Hr":94}

@ Jingyang Li I am getting this error when running your solution:

XML parsing: line 1, character 102, illegal qualified name character

• Edited by dvnandover Thursday, April 30, 2015 5:58 PM

This is the best forum ever.

I have decided to use Patrick Hurst suggestion and after doing some tweaking I have a useable solution right now.

Here is a couple minor issues:
Due to the large possible key/value pairs from the Json data  columns (up to 40 per column as I was told by the user) therefore it is impossible for me to list them all in my query. I am exploring the dynamic Pivot table solution as suggested by Patrick to see if I can pick up those column names dynamically and at the same time removing all empty/null value columns.

Edit: Ok  I might need a little more help to tackle these issues.

• Edited by dvnandover 15 hours 33 minutes ago

hmm, sounds still like a design issue ;)

If you want the values on a single line, you're kinda stuck dealing with the fact not all columns will be populated.

This is not the best way to store your values. The best way would be to store them in rows. The good news is, you already have this, it's the query you're already using, without the pivot:

DECLARE @table TABLE (Item1 VARCHAR(50), Item2 VARCHAR(50), Item3 VARCHAR(50), Item4 VARCHAR(50), Item5 VARCHAR(MAX), Item6 VARCHAR(MAX))
INSERT INTO @table (item1, item2, item3, item4, item5, item6) VALUES
('aaaaa', 'bbbbb', 'ccccc', 'ddddd',
'{"rulesOutputLabel":"lesionGreaterThanOneCmEp","benignFeatures":"no","benignByImagingOrBiopsy":"no","location":"rightLobe","size":10}',
'{"satisfiedContent":"no","satisfiedWording":"yes","preferredContent":"Shorter term f/u because of multiple GGN"}'),

('aaaab', 'bbbbb', 'ccccc', 'ddddd',
'{"rulesOutputLabel":"cat1NoLungNodulesEp","categoryOfFindings":"cat1NoLungNodules"}', NULL),
('aaaac', 'bbbbb', 'ccccc', 'ddddd',
'{"rulesOutputLabel":"shatteredSpleenEp","activeExtravasation":"evidencePseudoaneurysmYes","shatteredSpleenOrvascularInjury":"shatteredSpleen"}', NULL),
('aaaad', 'bbbbb', 'ccccc', 'ddddd',
'{"rulesOutputLabel":"gastricEmptyingEp1","pct4Hr":100,"pct2Hr":100,"pct1Hr":94}', NULL)

----------------------------------------------------------------------------------------


SELECT item1, item2, item3, item4, LEFT(value, CHARINDEX(':',value)-1) AS name, RIGHT(value, LEN(value)-CHARINDEX(':',value)) AS value
  FROM @table
    CROSS APPLY dbo.splitter(REPLACE(REPLACE(REPLACE(item5,'{',''),'"',''),'}',''), ',') five
UNION ALL
SELECT item1, item2, item3, item4, LEFT(value, CHARINDEX(':',value)-1) AS name, RIGHT(value, LEN(value)-CHARINDEX(':',value)) AS value
  FROM @table
	CROSS APPLY dbo.splitter(REPLACE(REPLACE(REPLACE(item6,'{',''),'"',''),'}',''), ',') six