Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Select Query problems

Posted on 2016-10-11
10
Medium Priority
?
53 Views
Last Modified: 2016-10-15
Hello EE,

in a table I got columns values Code, Desc01, Desc02, Desc03, Desc04 up to 15

in another table, I got columns:   Code, Qty01, Qty02, Qty03, Qty04 ... all to Qty15.

my question is, I need to read the second table but I dont want to show in headers Qty01 Qty02 ..
I want to show the real description based on the "Code value"

if code is R1
Desc01 to 15 will be let's say:   XXS, XS, S, M, L, XL, XXL, XXXL

if R2 it could be:   0 2 4 6 8 10 12 14 16

i want to show the good QTYS on the real description, without showing "Qty01" "Qty02"  you know what I mean ?

can you help on this,.. ?
0
Comment
Question by:PhilippeRenaud
[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
  • 2
  • 2
  • +1
10 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41838316
SELECT T1.*
FROM Table1 T1
    INNER JOINT Table2 T2 ON T1.Code = T2.Code

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41838323
if I do this, I will get columns name as  QTy01, Qty02 Qty03

this is not what I want
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41838324
No, it's just bringing columns from Table1 (T1).Qtys are from Table2 (T2).
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41838357
what do you want the output to show?
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41838370
If in my, lets call is TableA, I have :

Code    DEsc01   Desc02    Desc03    Desc04
R1         XXS          XS            S              M
R2          0             2               4              6


and in TableB I Got :

Code      QTY01       QTY02        QTY03        QTY04
R1           5                 0                3                  1
R2           1                 6                3                  10


if I do a query that I want to know the Qtys for R1 i woudl like the results to be like this :

XXS      XS       S       M
5          0          3      1
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41838382
if you want XXS, XS, ... to be the header, you will need to create a dynamic query.

if you want XXS, XS, ... to be the first row, this is much easier:
select DEsc01,  Desc02,    Desc03,    Desc04, ....
from tableA
where Code = 'R1'
union all
select QTY01,  QTY02,    QTY03,    QTY04, ....
from tableB
where Code = 'R1'

Open in new window




So what do you want?
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41838546
@Author - You want below as column Names?
XXS      XS       S       M
0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 41839003
yes !
0
 
LVL 32

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41839619
Here !! Pls  try..

Table Creation

--

CREATE TABLE table1
(
	 Code  VARCHAR(10)
	,DEsc01  VARCHAR(10) 
	,Desc02   VARCHAR(10) 
	,Desc03   VARCHAR(10) 
	,Desc04 VARCHAR(10)
)
GO

INSERT INTO table1 VALUES
('R1'     ,    'XXS' ,         'XS',            'S',              'M'),
('R2'      ,    '0'      ,       '2'      ,         '4',              '6')


CREATE TABLE table2
(
	 Code  VARCHAR(10)
	,QTY01  INT 
	,QTY02   INT
	,QTY03   INT
	,QTY04 INT
)
GO

INSERT INTO table2 VALUES
('R1'     ,      5 ,                0,                3,                  1),
('R2'     ,      1  ,               6 ,               3 ,                 10)
GO

--

Open in new window


Solution

--


DECLARE @Code AS VARCHAR(10) = 'R1'
DECLARE @Cols AS VARCHAR(1000) = ''

SELECT @Cols = @Cols + '[' + Desc01 + '],[' + Desc02  + '],[' + Desc03 + '],[' + Desc04 + ']' FROM table1 
WHERE Code = @Code

DECLARE @Query AS VARCHAR(MAX) = ''

SET @Query = '

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY code ORDER BY(SELECT 1)) rnk FROM 
	(
		SELECT *  FROM table1			
	)p
	WHERE code = ' + '''' + @code + '''' + ' 
)
SELECT ' +  @Cols +  ' FROM 
(
	SELECT *
	FROM
	(
		SELECT * FROM CTE c 
	)t
	UNPIVOT
	(
		rnku FOR t IN ( Desc01 , Desc02  , Desc03 , Desc04 )
	) AS Unvot
)p PIVOT
(
	MAX(t) FOR rnku IN ( ' +  @Cols  + ' )
)as pvt
WHERE rnk <> 1
UNION ALL
SELECT QTY01, QTY02 , QTY03 , QTY04 FROM table2 
WHERE Code = ' + '''' + @code + ''''

EXEC (@Query)


--

Open in new window



Output for Code = R1

XXS      XS      S      M
5      0      3      1



Output for Code = R2

0      2      4      6
1      6      3      10


Hope it helps !!
0
 
LVL 1

Author Closing Comment

by:PhilippeRenaud
ID: 41845008
thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

618 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